Market-Mix-Modelling: Budget_Optimization¶
Objective¶
ElecKart is an e-commerce firm based out of Ontario, Canada specialising in electronic products. Over the last one year, they had spent a significant amount of money on marketing. Occasionally, they had also offered big-ticket promotions (similar to the Big Billion Day). They are about to create a marketing budget for the next year, which includes spending on commercials, online campaigns, and pricing & promotion strategies. The CFO feels that the money spent over the last 12 months on marketing was not sufficiently impactful, and, that they can either cut on the budget or reallocate it optimally across marketing levers to improve the revenue response.
Imagine that you are a part of the marketing team working on budget optimisation. You need to develop a market mix model to observe the actual impact of different marketing variables over the last year. Using your understanding of the model, you have to recommend the optimal budget allocation for different marketing levers for the next year.*
The objective of this project is to create a market mix model for 3 product sub-categories - Camera accessory, Gaming accessory and Home Audio - to observe the actual impact of various marketing variables over one year (July 2015 to June 2016) and recommend the optimal budget allocation for different marketing levers for the next year.
Step 1: Importing import packages¶
In [1]:
# import libraries
import pandas as pd
import numpy as np
# For Visualisation
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline
import matplotlib
# Supress Warnings
import warnings
warnings.filterwarnings('ignore')
# Pandas Settings
# pd.options.display.float_format = '{:.1f}'.format
pd.set_option('display.max_rows', 40000)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
# Miscellaneous imports
from datetime import datetime
from scipy.stats import norm
import re
Step 2: Reading Data¶
Reading the main data file - ConsumerElectronics.csv into a dataframe¶
In [2]:
main_df = pd.read_csv('ConsumerElectronics.csv')
main_df.head()
Out[2]:
Understanding some attributes¶
SKU => Stock Keeping Unit
deliverybdays => days to get item or order from warehouse for shipping
deliverycdays => days to deliver item to customer
Reading the product list excel data file into a dataframe¶
In [3]:
product_list = pd.read_excel('Media data and other information.xlsx', sheet_name='Product List')
product_list.columns.values[1]='product_analytic_vertical'
product_list.drop(product_list.columns[0], axis=1, inplace = True)
product_list.head()
Out[3]:
Reading the Media Investment excel data file into a dataframe¶
In [4]:
media_investment = pd.read_excel('Media data and other information.xlsx', sheet_name='Media Investment', skiprows=2)
media_investment.drop(media_investment.columns[0], axis=1, inplace = True)
media_investment.head()
Out[4]:
Reading the Sale Calendar excel data file into a dataframe¶
In [5]:
sale_calendar = pd.read_excel('Media data and other information.xlsx', sheet_name='Special Sale Calendar', \
skiprows=0, skipfooter=2)
sale_calendar.drop(sale_calendar.columns[0], axis=1, inplace = True)
sale_calendar.iloc[1:6, 0] = sale_calendar.iloc[0, 0]
sale_calendar.iloc[7:, 0] = sale_calendar.iloc[6, 0]
sale_calendar
Out[5]:
Reading the net_promoter_score excel file into a dataframe¶
In [6]:
net_promoter_score = pd.read_excel('Media data and other information.xlsx', sheet_name='Monthly NPS Score', \
skiprows=0)
net_promoter_score.columns.values[0]='score'
net_promoter_score
Out[6]:
Reading the product details word file into a dataframe¶
In [7]:
# pip install --pre python-docx
import pandas as pd
import io
import csv
from docx import Document
def read_docx_tables(filename, tab_id=None, **kwargs):
"""
parse table(s) from a Word Document (.docx) into Pandas DataFrame(s)
Parameters:
filename: file name of a Word Document
tab_id: parse a single table with the index: [tab_id] (counting from 0).
When [None] - return a list of DataFrames (parse all tables)
kwargs: arguments to pass to `pd.read_csv()` function
Return: a single DataFrame if tab_id != None or a list of DataFrames otherwise
"""
def read_docx_tab(tab, **kwargs):
vf = io.StringIO()
writer = csv.writer(vf)
for row in tab.rows:
writer.writerow(cell.text for cell in row.cells)
vf.seek(0)
return pd.read_csv(vf, **kwargs)
doc = Document(filename)
if tab_id is None:
return [read_docx_tab(tab, **kwargs) for tab in doc.tables]
else:
try:
return read_docx_tab(doc.tables[tab_id], **kwargs)
except IndexError:
print('Error: specified [tab_id]: {} does not exist.'.format(tab_id))
raise
dfs = read_docx_tables('Product Details.docx')
dfs[0]
Out[7]:
In [8]:
# Dropping the extra row(s)/column(s)
product_details = pd.DataFrame(dfs[0])
product_details.drop(product_details.columns[0], axis=1, inplace = True)
product_details.head()
Out[8]:
Step 3: Cleaning Data¶
In [9]:
main_df.head()
Out[9]:
In [10]:
main_df.dtypes
Out[10]:
Correcting Data Types¶
In [11]:
# String to datetime
main_df['order_date'] = pd.to_datetime(main_df['order_date'], format='%Y-%m-%d %H:%M:%S')
In [12]:
# Int to string
main_df[['order_id','order_item_id']] = main_df[['order_id','order_item_id']].astype(object)
In [13]:
# Int to string
main_df[['Year','Month']] = main_df[['Year','Month']].astype(str)
Assuming "\N" value in deliverybdays & deliverycdays is equal to 0, will impute the nulls created by it with 0¶
In [14]:
main_df['deliverybdays'] = pd.to_numeric(main_df['deliverybdays'], errors='coerce')
main_df['deliverybdays'].fillna(value=0, inplace=True)
main_df['deliverycdays'] = pd.to_numeric(main_df['deliverycdays'], errors='coerce')
main_df['deliverycdays'].fillna(value=0, inplace=True)
In [15]:
# String to int
main_df['gmv'] = pd.to_numeric(main_df['gmv'], errors='coerce')
In [16]:
main_df.dtypes
Out[16]:
Unique Values¶
In [17]:
# Unique value frequencies
unique_values = pd.DataFrame(main_df.apply(lambda x: len(x.value_counts(dropna=False)), axis=0), columns=['Unique Value Count']).sort_values(by='Unique Value Count', ascending=True)
unique_values['dtype'] = pd.DataFrame(main_df.dtypes)
unique_values
Out[17]:
In [18]:
initial_shape = main_df.shape
initial_shape
Out[18]:
Fix Invalid Values¶
Treating incorrect GMV values w.r.t product_mrp * units¶
In [19]:
# Instances where GMV values are greater than MRP * units which is incorrect
print(main_df.loc[main_df['product_mrp'] * main_df['units'] < main_df['gmv']].shape[0])
print(round(100*(main_df.loc[main_df['product_mrp'] * main_df['units'] < main_df['gmv']].shape[0] / main_df.shape[0]), 2))
There are 38569 records (2.34%) in the dataframe where the GMV value is greater than the MRP * units.
We will be imputing the faulty mrp values with gmv/units¶
In [20]:
# update column based on another column
main_df.product_mrp = np.where(main_df['product_mrp'] < main_df['gmv'] / main_df['units'], \
main_df['gmv'] / main_df['units'], main_df['product_mrp'])
main_df.shape
Out[20]:
Reinspecting instances where GMV value is greater than the MRP * units.
In [21]:
# Instances where GMV values are greater than MRP * units which is incorrect
print(main_df.loc[main_df['product_mrp'] * main_df['units'] < main_df['gmv']].shape[0])
print(round(100*(main_df.loc[main_df['product_mrp'] * main_df['units'] < main_df['gmv']].shape[0] / main_df.shape[0]), 2))
All erroneous instances were removed
Treating GMV values less than 0¶
In [22]:
print(main_df.loc[main_df['gmv'] < 0].shape[0])
print(round(100*(main_df.loc[main_df['gmv'] < 0].shape[0]/main_df.shape[0]), 2))
Treating MRP values less than or equal to 0¶
In [23]:
print(main_df.loc[main_df['product_mrp'] < 0].shape[0])
print(round(100*(main_df.loc[main_df['product_mrp'] < 0].shape[0]/main_df.shape[0]), 2))
Treating Units values less than or equal to 0¶
In [24]:
print(main_df.loc[main_df['units'] <= 0].shape[0])
print(round(100*(main_df.loc[main_df['units'] <= 0].shape[0]/main_df.shape[0]), 2))
No erroneous rows left in MRP, GMV or Units columns
Handling Negative values for deliverybdays & deliverycdays¶
In [25]:
print(main_df.loc[main_df['deliverybdays'] < 0].shape[0])
print(round(100*(main_df.loc[main_df['deliverybdays'] < 0].shape[0]/main_df.shape[0]),4))
print(main_df.loc[main_df['deliverycdays'] < 0].shape[0])
print(round(100*(main_df.loc[main_df['deliverycdays'] < 0].shape[0]/main_df.shape[0]),4))
- There are
38records(0.0023%) in the dataframe with negative values for deliverybdays. - There are
39records(0.0024%) in the dataframe with negative values for deliverycdays.
We will be dropping such rows since it is not possible for a product to have negative values for Dispatch Delay from Warehouse and to customer.
In [26]:
main_df = main_df.loc[(main_df['deliverybdays'] >= 0) & (main_df['deliverycdays'] >= 0)]
main_df.reset_index(drop=True, inplace=True)
In [27]:
main_df.shape
Out[27]:
Handling Negative values for product_procurement_sla¶
In [28]:
print(main_df.loc[main_df['product_procurement_sla'] < 0].shape[0])
print(round(100*(main_df.loc[main_df['product_procurement_sla'] < 0].shape[0]/main_df.shape[0]),4))
- There are
75986records(4.61%) in the dataframe with negative values for product_procurement_sla.
We will be dropping such rows since it is not possible for a product to have negative values for time typically taken to procure it.
In [29]:
main_df = main_df.loc[(main_df['product_procurement_sla'] >= 0)]
main_df.reset_index(drop=True, inplace=True)
In [30]:
main_df.shape
Out[30]:
Handling large values for product_procurement_sla¶
In [31]:
main_df[['product_procurement_sla']].describe().T
Out[31]:
In [32]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(6, 4), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("whitegrid") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
main_df.product_procurement_sla.hist()
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
In [33]:
main_df.product_procurement_sla.value_counts()
Out[33]:
In [34]:
print(main_df.loc[main_df['product_procurement_sla'] >= 1000].shape[0])
print(round(100*(main_df.loc[main_df['product_procurement_sla'] >= 1000].shape[0]/main_df.shape[0]),4))
- There are
4745records(0.3%) in the dataframe with unusually large values of product_procurement_sla.
We will be dropping such rows since it is not possible for a product to have product_procurement_sla more than 1000
Retaining rows where the sla is less than 1000
In [35]:
#Retaining rows where the sla is less than 1000
main_df = main_df.loc[(main_df['product_procurement_sla'] < 1000)]
main_df.head()
Out[35]:
In [36]:
main_df.shape
Out[36]:
In [37]:
main_df.info()
De-Duplicate Data¶
Make all string columns lower case¶
In [38]:
cat_cols = [cname for cname in main_df.columns if main_df[cname].dtype == "object"]
cat_cols
Out[38]:
In [39]:
# Filtering the string columns
cat_cols = ['fsn_id',
's1_fact.order_payment_type',
'product_analytic_super_category',
'product_analytic_category',
'product_analytic_sub_category',
'product_analytic_vertical']
for col in cat_cols:
main_df[col] = main_df[col].str.lower()
Spelling check by investigating the unique values¶
In [40]:
cat_cols = [cname for cname in main_df.columns if main_df[cname].dtype == "object"]
main_df[cat_cols].describe()
Out[40]:
In [41]:
# Checking only those columns whose cardinality is less
selected_cat_cols = ['s1_fact.order_payment_type', 'product_analytic_super_category', 'product_analytic_category', 'product_analytic_sub_category', 'product_analytic_vertical']
for col in selected_cat_cols:
print('\n################################')
print('Unique values of ' + str(col))
print('################################')
print(pd.Series(main_df[col].unique()).sort_values(ascending=False))
There are no duplicates due to spelling mistakes
Drop duplicate rows¶
In [42]:
main_df.shape
Out[42]:
In [43]:
print(main_df.duplicated().value_counts()[1])
print(round(100*(main_df.duplicated().value_counts()[1]/main_df.shape[0]),4))
Around 99283 (6.33%) rows have duplicates. We will go ahead and drop them.¶
In [44]:
main_df.drop_duplicates(keep='first', inplace=True)
main_df.shape
Out[44]:
In [45]:
main_df.duplicated().value_counts()
Out[45]:
No more duplicates
Treating Nulls¶
In [46]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(main_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(main_df.isnull().sum()/main_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[46]:
There are a few null values in the gmv column.
Also, there are quite a few Whitespaces present in some of the columns in the dataframe.
Let us try to first convert these white spaces to Nans and then we would treat them accordingly.
In [47]:
main_df.replace(' ', np.nan, inplace = True)
Again trying to find null percentage in columns¶
In [48]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(main_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(main_df.isnull().sum()/main_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[48]:
Removing rows where a particular column has high missing values¶
In [49]:
# removing rows where a particular column has high missing values because the column cannot be removed because of its importance
main_df = main_df[~pd.isnull(main_df['gmv'])]
org_shape = main_df.shape
org_shape
Out[49]:
Again trying to find null percentage in columns¶
In [50]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(main_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(main_df.isnull().sum()/main_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[50]:
Finally no Null rows exist in the dataframe
Changing the datatype of the column 'order_date' to datetime64
In [51]:
main_df.dtypes
Out[51]:
Identifying the data outside our analysis period - 01-Jul-2015 to 30-Jun-2016
In [52]:
main_df.loc[(main_df['order_date'].dt.floor("d") < '2015-07-01') | (main_df['order_date'].dt.floor("d") >= '2016-07-01')].shape
Out[52]:
There are 592 records that fall outside our analysis period - 01-Jul-2015 to 30-Jun-2016
We will be dropping these records.
In [53]:
main_df = main_df.loc[(main_df['order_date'].dt.floor("d") >= '2015-07-01') & (main_df['order_date'].dt.floor("d") < '2016-07-01')]
main_df.shape
Out[53]:
In [54]:
Max = pd.DataFrame(main_df[['order_date']].max().rename('Max'))
Min = pd.DataFrame(main_df[['order_date']].min().rename('Min'))
pd.concat([Min, Max], axis=1)
Out[54]:
This verifies that our dataframe has data between July 2015 and June 2016 only.
Generate Week Column¶
In [55]:
# Creating a new column into a specific position in a DataFrame
loc_index = main_df.columns.get_loc('Month') + 1
main_df.insert(loc=loc_index,column='Week',value=main_df['order_date'].dt.strftime("%V"))
main_df['Year'] = main_df['Year'].astype('str')
main_df.head()
Out[55]:
In [56]:
# Checking the combinations for any discrepancies
main_df.groupby(['Year', 'Week']).agg({'Month':"count"}).reset_index(drop=False)
Out[56]:
In [57]:
# Updating the year as 2015 for the week whose week# is 53 but belongs to 2016 for consistency with the other data sets
# Updating the month as 12 for the above rows for consistency
# Dropping rows (less in number) with week# 27 as it belongs to the weeks in June 2015
main_df.loc[(main_df.Year == '2016') & (main_df.Week == '53'), 'Year'] = '2015'
main_df.loc[(main_df.Year == '2015') & (main_df.Week == '53'), 'Month'] = 12
main_df.drop(main_df[main_df['Week'] == '27'].index, inplace = True)
In [58]:
main_df.groupby(['Year', 'Week']).agg({'Month':"count"}).reset_index(drop=False)
Out[58]:
Drop Insignificant Columns¶
In [59]:
main_df.info()
In [60]:
# Dropping Columns with Single Value or all Different Values
count_df = pd.DataFrame(main_df.apply(lambda x: len(x.value_counts()), axis=0), columns=['Count'])
drop_columns = list(count_df.loc[(count_df['Count']==1) | (count_df['Count']==len(main_df.index))].index)
print('Dropping these columns => {}'.format(drop_columns))
main_df.drop(drop_columns, axis=1, inplace=True)
In [61]:
# Dropping Columns which are insignificant to the analysis
drop_columns = ['fsn_id', 'order_id', 'order_item_id', 'cust_id']
main_df.drop(drop_columns, axis=1, inplace=True)
Feature Engineering¶
In [62]:
main_df.head()
Out[62]:
Creating column List Price¶
List Price = GMV/Units
In [63]:
# Creating a new column into a specific position in a DataFrame
loc_index = main_df.columns.get_loc('gmv') + 1
main_df.insert(loc=loc_index,column='list_price',value = main_df['gmv'] / main_df['units'])
main_df.head()
Out[63]:
Displaying the column values for a few orders when units > 1
In [64]:
main_df.loc[main_df['units'] != 1][['gmv','list_price','product_mrp','units']].head()
Out[64]:
Creating a Payday Flag (+- 1 for salary days)¶
If it is nearer to the salary day in Ontario(1st and 15th of every month), we flag the column as 1, else as 0
In [65]:
main_df['payday_flag'] = main_df['order_date'].apply(lambda x:1 if x.strftime('%d') in ('14','15','16','30','31','1','2') else 0)
main_df.head()
Out[65]:
Creating an Occassion Flag¶
If it is a Holiday/Occassion in Ontario, we flag the column as 1, else as 0
The following table lists all the holidays/occassions from 1st July, 2015 to 30th June, 2016**
| Occassion | Day |
|---|---|
| Canada Day | July 1, 2015 |
| Civic Holiday | August 3, 2015 |
| Labour Day | September 7, 2015 |
| Thanksgiving | October 12, 2015 |
| Halloween | October 31, 2015 |
| Remembrance Day | November 11, 2015 |
| Christmas Day | December 25, 2015 |
| Boxing Day | December 26, 2015 |
| New Year's Day | January 1, 2016 |
| Islander Day | February 15, 2016 |
| Louis Riel Day | February 15, 2016 |
| Heritage Day | February 15, 2016 |
| Family Day | February 15, 2016 |
| Valentine's Day | February 14, 2016 |
| Leap Day | February 29, 2016 |
| St. Patrick's Day | March 17, 2016 |
| Good Friday | March 25, 2016 |
| Easter Monday | March 28, 2016 |
| Mother's Day | May 8, 2016 |
| Victoria Day | May 23, 2016 |
| Father's Day | June 19, 2016 |
| Aboriginal Day | June 21, 2016 |
| St. Jean Baptiste Day | June 24, 2016 |
**reference link: https://www.statutoryholidays.com/
In [66]:
def holidayflg(ord_date):
if ord_date.strftime('%Y') == 2015:
if ord_date.strftime('%m') == '07' and ord_date.strftime('%d') == '01':
return 1
elif ord_date.strftime('%m') == '08' and ord_date.strftime('%d') == '03':
return 1
elif ord_date.strftime('%m') == '09' and ord_date.strftime('%d') == '07':
return 1
elif ord_date.strftime('%m') == '10' and ord_date.strftime('%d') == '12':
return 1
elif ord_date.strftime('%m') == '10' and ord_date.strftime('%d') == '31':
return 1
elif ord_date.strftime('%m') == '11' and ord_date.strftime('%d') == '11':
return 1
elif ord_date.strftime('%m') == '12' and ord_date.strftime('%d') == '25':
return 1
elif ord_date.strftime('%m') == '12' and ord_date.strftime('%d') == '26':
return 1
else:
return 0
else:
if ord_date.strftime('%m') == '01' and ord_date.strftime('%d') == '01':
return 1
elif ord_date.strftime('%m') == '02' and ord_date.strftime('%d') == '15':
return 1
elif ord_date.strftime('%m') == '02' and ord_date.strftime('%d') == '14':
return 1
elif ord_date.strftime('%m') == '02' and ord_date.strftime('%d') =='29':
return 1
elif ord_date.strftime('%m') == '03' and ord_date.strftime('%d') == '17':
return 1
elif ord_date.strftime('%m') == '03' and ord_date.strftime('%d') == '25':
return 1
elif ord_date.strftime('%m') == '03' and ord_date.strftime('%d') == '28':
return 1
elif ord_date.strftime('%m') == '05' and ord_date.strftime('%d') == '08':
return 1
elif ord_date.strftime('%m') == '05' and ord_date.strftime('%d') == '23':
return 1
elif ord_date.strftime('%m') == '06' and ord_date.strftime('%d') == '19':
return 1
elif ord_date.strftime('%m') == '06' and ord_date.strftime('%d') == '21':
return 1
elif ord_date.strftime('%m') == '06' and ord_date.strftime('%d') == '24':
return 1
else:
return 0
In [67]:
main_df['occassion_flag'] = main_df['order_date'].apply(lambda x:holidayflg(x))
main_df.head()
Out[67]:
Creating a column called Product Type - Luxury / Mass_market¶
If GMV value is greater than 80 percentile, then luxury, else mass_market
In [68]:
pd.DataFrame(main_df['gmv']).describe(percentiles=[.70,.80,.90]).T
Out[68]:
In [69]:
main_df['gmv'].quantile(.8)
Out[69]:
In [70]:
main_df['product_type'] = main_df['gmv'].apply(lambda x:'luxury' if x >= 2450 else 'mass_market')
main_df.head()
Out[70]:
In [71]:
# Creating a new column into a specific position in a DataFrame
col_loc = main_df.columns.get_loc('list_price') + 1
main_df.insert(loc=col_loc, column='Discount%', value = \
round(100*((main_df['product_mrp'] - main_df['list_price']) / main_df['product_mrp']),2))
main_df.head()
Out[71]:
Statistical Info¶
Numeric columns¶
In [72]:
main_df.describe().T
Out[72]:
Distribution of the numeric columns¶
In [73]:
for col in main_df.describe().columns:
print('#############')
print(col)
print('#############')
main_df[col].hist()
plt.show()
Categorical columns¶
In [74]:
cat_var = [cname for cname in main_df.columns if
main_df[cname].dtype == "object"]
main_df[cat_var].describe().T
Out[74]:
Outlier Treatment¶
In [75]:
main_df.head()
Out[75]:
In [76]:
main_df.dtypes
Out[76]:
In [77]:
# Function to plot the distribution plot of the numeric variable list
numeric_variables=['gmv','list_price','Discount%','deliverybdays','deliverycdays','sla','product_mrp','product_procurement_sla']
#Function to plot the distribution plot of the numeric variable list
def univariate_continuos(var_list):
plt.figure(figsize=(12,6))
for var in var_list:
plt.subplot(2,4,var_list.index(var)+1)
#plt.boxplot(country[var])
sns.boxplot(y=var,palette='cubehelix', data=main_df)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
In [78]:
univariate_continuos(numeric_variables)
There seems to be a lot of outliers in the columns. But all outliers may not be at random and we will have to be careful regarding which ones to drop.
In [79]:
# Checking outliers at 25%,50%,75%,90%,95% and 99%
main_df[numeric_variables].describe(percentiles=[.25,.5,.75,.90,.95,.99]).T
Out[79]:
After analyzing the distribution of values of the above attributes at different percentiles, we come to the following conclusion:¶
'gmv', 'list_price' and 'mrp'values can have occassional outliers owing to the fact that there are seldom some high value sales. These records are less frequent and not at all at random.'Discount%'can also be more in case on certain products. Specifically, a discount percentage of 100% would mena, the product came free with some other product.- On ther hand, unusually high values of
'deliverybdays', 'deliverycdays' and 'sla'seem to be erroneous data. So we will find a way to treat these outlier values so that they do not affect the predictive model while at the same time there will be enough data to build a generalizable model.
In [80]:
main_df[['deliverybdays', 'deliverycdays' ,'sla']].quantile([.95, .99])
Out[80]:
Let us assume any observation above 99 percentile for a variable is considered as an outlier for that variable.
Percentage of Outliers in column deliverybdays¶
In [81]:
print(round(100*(main_df.loc[main_df['deliverybdays'] > 8.0].shape[0] / main_df.shape[0]),4))
Percentage of Outliers in column deliverycdays¶
In [82]:
print(round(100*(main_df.loc[main_df['deliverycdays'] > 10.0].shape[0] / main_df.shape[0]),4))
Percentage of Outliers in column sla¶
In [83]:
print(round(100*(main_df.loc[main_df['sla'] > 13.0].shape[0] / main_df.shape[0]),4))
Capping value above or below a certain percentile:¶
For the variables - 'SLA', 'deliverybdays', 'deliverybdays', 'gmv', 'product_mrp', 'list_price' where outliers are present, we will CAP the values above 99 percentile to the value corresponding to 99 percentile.
In [84]:
# Updating the outlier values with values corresponding to pre-decided percentiles
main_df['deliverybdays'][np.abs(main_df['deliverybdays'] > 8.0)]= 8.0
main_df['deliverybdays'][np.abs(main_df['deliverybdays'] > 10.0)]= 10.0
main_df['sla'][np.abs(main_df['sla'] > 13.0)]= 13.0
In [85]:
print(round(100*(main_df.shape[0] / initial_shape[0])))
So 89% records have been retained after outlier treatment
In [1]:
# Checking outliers at 25%,50%,75%,90%,95% and 99%
main_df.describe(percentiles=[.25,.5,.75,.90,.95,.99]).T
In [87]:
univariate_continuos(numeric_variables)
Skewness¶
In [88]:
# Checking the skewness in the dataset
main_df[numeric_variables].skew()
Out[88]:
In [89]:
num_col = ['gmv','deliverybdays','deliverycdays','list_price','product_mrp','product_procurement_sla']
#Function to plot the distribution plot of the numeric variable list
def distplots(var_list):
plt.figure(figsize=(15,6))
for var in var_list:
plt.subplot(2,3,var_list.index(var)+1)
sns.distplot(main_df[var], fit=norm, kde=False, hist=False)
#sns.boxplot(y=var,palette='cubehelix', data=main_df)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
distplots(num_col)
In [90]:
main_df.isnull().values.any()
Out[90]:
Mapping months to number of weeks for the month¶
Below function takes input an year and generates the numbers of the weeks for each month in it
In [91]:
import calendar
def WeekFinderFromYear(year):
""" will return all the week from selected year """
import datetime
WEEK = {'MONDAY':0,'TUESDAY':1,'WEDNESDAY':2,'THURSDAY':3,'FRIDAY':4,'SATURDAY':5,'SUNDAY':6}
MONTH = {'JANUARY':1,'FEBRUARY':2,'MARCH':3,'APRIL':4,'MAY':5,'JUNE':6,'JULY':7,'AUGUST':8,'SEPTEMBER':9,'OCTOBER':10,'NOVEMBER':11,'DECEMBER':12}
year = int(year)
month = MONTH['JANUARY']
day = WEEK['MONDAY']
dt = datetime.date(year, month, 1)
dow_lst = []
while dt.weekday() != day:
dt = dt + datetime.timedelta(days = 1)
lst_month = MONTH.values()
for mont in lst_month:
while dt.month == mont:
dow_lst.append(dt)
dt = dt + datetime.timedelta(days=7)
month_number_week = {1:[], 2:[], 3:[], 4:[], 5:[], 6:[], 7:[], 8:[], 9:[], 10:[], 11:[], 12:[]}
month_name_week = {'Jan':[], 'Feb':[], 'Mar':[], 'Apr':[], 'May':[], 'Jun':[], 'Jul':[], 'Aug':[], 'Sep':[], 'Oct':[], 'Nov':[], 'Dec':[]}
for each in dow_lst:
month_number_week[each.month].append(each.isocalendar()[1])
month_name_week[calendar.month_abbr[each.month]].append(each.isocalendar()[1])
return month_number_week, month_name_week
In [92]:
dict_number_2015, dict_name_2015 = WeekFinderFromYear(2015)
print(dict_number_2015)
print()
print(dict_name_2015)
print()
dict_number_2016, dict_name_2016 = WeekFinderFromYear(2016)
print(dict_number_2016)
print()
print(dict_name_2016)
In [93]:
num_weeks_2015 = 0
num_weeks_2016 = 0
for i in dict_number_2015:
if i >= 7:
num_weeks_2015 += len(dict_number_2015[i])
for i in dict_number_2016:
if i <= 6:
num_weeks_2016 += len(dict_number_2016[i])
total_weeks = num_weeks_2015 + num_weeks_2016
total_weeks
Out[93]:
As expected there are a total of 52 weeks in our dataset
Media Investment¶
We will generate weekly data from Year and Month for the media investment data
In [94]:
media_investment.head()
Out[94]:
In [95]:
# Imputing nulls with 0
media_investment['Radio'].fillna(value=0, inplace=True)
media_investment['Other'].fillna(value=0, inplace=True)
media_investment.head()
Out[95]:
In [96]:
# Temp DataFrame
temp_media_investment = pd.DataFrame(index=range(total_weeks), columns=['Year', 'Month', 'Week', 'Total Investment', 'TV', 'Digital', 'Sponsorship', 'Content Marketing', 'Online marketing', 'Affiliates', 'SEM', 'Radio', 'Other'])
temp_media_investment.head()
Out[96]:
We divide the monthly values by the number of weeks in that month and take that as weekly data
In [97]:
i = 0
for index, row in media_investment.iterrows():
if row.Year == 2015:
num_weeks = len(dict_number_2015[row.Month])
for week in dict_number_2015[row.Month]:
temp_media_investment.iloc[i].Year = row.Year
temp_media_investment.iloc[i].Month = row.Month
temp_media_investment.iloc[i]['Week'] = week
temp_media_investment.iloc[i]['Total Investment'] = round(row['Total Investment'] / num_weeks, 3)
temp_media_investment.iloc[i]['TV'] = round(row['TV'] / num_weeks, 3)
temp_media_investment.iloc[i]['Digital'] = round(row['Digital'] / num_weeks, 3)
temp_media_investment.iloc[i]['Sponsorship'] = round(row['Sponsorship'] / num_weeks, 3)
temp_media_investment.iloc[i]['Content Marketing'] = round(row['Content Marketing'] / num_weeks, 3)
temp_media_investment.iloc[i]['Online marketing'] = round(row['Online marketing'] / num_weeks, 3)
temp_media_investment.iloc[i]['Affiliates'] = round(row[' Affiliates'] / num_weeks, 3)
temp_media_investment.iloc[i]['SEM'] = round(row['SEM'] / num_weeks, 3)
temp_media_investment.iloc[i]['Radio'] = round(row['Radio'] / num_weeks, 3)
temp_media_investment.iloc[i]['Other'] = round(row['Other'] / num_weeks, 3)
i+=1
elif row.Year == 2016:
num_weeks = len(dict_number_2016[row.Month])
for week in dict_number_2016[row.Month]:
temp_media_investment.iloc[i].Year = row.Year
temp_media_investment.iloc[i].Month = row.Month
temp_media_investment.iloc[i]['Week'] = week
temp_media_investment.iloc[i]['Total Investment'] = round(row['Total Investment'] / num_weeks, 3)
temp_media_investment.iloc[i]['TV'] = round(row['TV'] / num_weeks, 3)
temp_media_investment.iloc[i]['Digital'] = round(row['Digital'] / num_weeks, 3)
temp_media_investment.iloc[i]['Sponsorship'] = round(row['Sponsorship'] / num_weeks, 3)
temp_media_investment.iloc[i]['Content Marketing'] = round(row['Content Marketing'] / num_weeks, 3)
temp_media_investment.iloc[i]['Online marketing'] = round(row['Online marketing'] / num_weeks, 3)
temp_media_investment.iloc[i]['Affiliates'] = round(row[' Affiliates'] / num_weeks, 3)
temp_media_investment.iloc[i]['SEM'] = round(row['SEM'] / num_weeks, 3)
temp_media_investment.iloc[i]['Radio'] = round(row['Radio'] / num_weeks, 3)
temp_media_investment.iloc[i]['Other'] = round(row['Other'] / num_weeks, 3)
i+=1
temp_media_investment.head()
Out[97]:
In [98]:
temp_media_investment['Week'] = temp_media_investment['Week'].astype('str')
In [99]:
media_investment = temp_media_investment
media_investment.head()
Out[99]:
In [100]:
# Checking for duplicates
media_investment.duplicated('Week').value_counts()
Out[100]:
No duplicates
In [101]:
# Checking for nulls
media_investment.isnull().values.any()
Out[101]:
No Nulls
In [102]:
# Dropping Year and Month columns as we won't be needing them anymore
del media_investment['Year']
del media_investment['Month']
media_investment.head()
Out[102]:
In [103]:
original_col = media_investment.columns[1:]
original_col
Out[103]:
Calculating 8-weeks Exponential Moving Average for all Advertising media channels¶
In [104]:
def EMA_variables(df,var,n):
for i in var:
loc_index = df.columns.get_loc(i) + 1
df.insert(loc=loc_index,column= i+'_EMA_'+np.str(n),value=df[i].ewm(span=n, adjust=False).mean())
return df
In [105]:
media_investment = EMA_variables(media_investment,original_col,8)
media_investment.head()
Out[105]:
Calculating 5-weeks Simple Moving Average for all Advertising media channels¶
In [106]:
def SMA_variables(df,var,n):
for i in var:
loc_index = df.columns.get_loc(i) + 1
df.insert(loc=loc_index,column= i+'_SMA_'+np.str(n),value=df[i].rolling(window=n).mean())
return df
In [107]:
media_investment = SMA_variables(media_investment,original_col,5)
media_investment.head()
Out[107]:
Calculating 3-weeks Simple Moving Average for all Advertising media channels¶
In [108]:
media_investment = SMA_variables(media_investment,original_col,3)
media_investment.head()
Out[108]:
Calculating Ad Stock values for all Advertising media¶
Generating TV Ad Stock values¶
In [109]:
def calculate_ad_stocks(data, engagement_factor):
"""initialize ad stock vectors"""
TV_ad_stock_vector = []
Digital_ad_stock_vector = []
Sponsorship_ad_stock_vector = []
Content_Marketing_ad_stock_vector = []
Online_marketing_ad_stock_vector = []
Affiliates_ad_stock_vector = []
SEM_ad_stock_vector = []
Radio_ad_stock_vector = []
Other_ad_stock_vector = []
Total_Investment_ad_stock_vector = []
"""initialize ad_stock_value"""
tv_ad_stock_value = 0
Digital_ad_stock_value = 0
Sponsorship_ad_stock_value = 0
Content_Marketing_ad_stock_value = 0
Online_marketing_ad_stock_value = 0
Affiliates_ad_stock_value = 0
SEM_ad_stock_value = 0
Radio_ad_stock_value = 0
Other_ad_stock_value = 0
Total_Investment_ad_stock_value = 0
"""loop through dataset to calculate adstock values. The formula for adstock is: At = Xt + adstock rate * At-1."""
for index, row in data.iterrows():
tv_ad_stock_value = row['TV'] + engagement_factor * tv_ad_stock_value
TV_ad_stock_vector.append(tv_ad_stock_value)
Digital_ad_stock_value = row['Digital'] + engagement_factor * Digital_ad_stock_value
Digital_ad_stock_vector.append(Digital_ad_stock_value)
Sponsorship_ad_stock_value = row['Sponsorship'] + engagement_factor * Sponsorship_ad_stock_value
Sponsorship_ad_stock_vector.append(Sponsorship_ad_stock_value)
Content_Marketing_ad_stock_value = row['Content Marketing'] + engagement_factor * Content_Marketing_ad_stock_value
Content_Marketing_ad_stock_vector.append(Content_Marketing_ad_stock_value)
Online_marketing_ad_stock_value = row['Online marketing'] + engagement_factor * Online_marketing_ad_stock_value
Online_marketing_ad_stock_vector.append(Online_marketing_ad_stock_value)
Affiliates_ad_stock_value = row['Affiliates'] + engagement_factor * Affiliates_ad_stock_value
Affiliates_ad_stock_vector.append(Affiliates_ad_stock_value)
SEM_ad_stock_value = row['SEM'] + engagement_factor * SEM_ad_stock_value
SEM_ad_stock_vector.append(SEM_ad_stock_value)
Radio_ad_stock_value = row['Radio'] + engagement_factor * Radio_ad_stock_value
Radio_ad_stock_vector.append(Radio_ad_stock_value)
Other_ad_stock_value = row['Other'] + engagement_factor * Other_ad_stock_value
Other_ad_stock_vector.append(Other_ad_stock_value)
Total_Investment_ad_stock_value = row['Total Investment'] + engagement_factor * Total_Investment_ad_stock_value
Total_Investment_ad_stock_vector.append(Total_Investment_ad_stock_value)
"""add ad stock vector to dataset"""
loc_index = data.columns.get_loc('TV_EMA_8') + 1
data.insert(loc=loc_index,column='TV_Ad_Stock',value=TV_ad_stock_vector)
loc_index = data.columns.get_loc('Digital_EMA_8') + 1
data.insert(loc=loc_index,column='Digital_Ad_Stock',value=Digital_ad_stock_vector)
loc_index = data.columns.get_loc('Sponsorship_EMA_8') + 1
data.insert(loc=loc_index,column='Sponsorship_Ad_Stock',value=Sponsorship_ad_stock_vector)
loc_index = data.columns.get_loc('Content Marketing_EMA_8') + 1
data.insert(loc=loc_index,column='Content_Marketing_Ad_Stock',value=Content_Marketing_ad_stock_vector)
loc_index = data.columns.get_loc('Online marketing_EMA_8') + 1
data.insert(loc=loc_index,column='Online_marketing_Ad_Stock',value=Online_marketing_ad_stock_vector)
loc_index = data.columns.get_loc('Affiliates_EMA_8') + 1
data.insert(loc=loc_index,column='Affiliates_Ad_Stock',value=Affiliates_ad_stock_vector)
loc_index = data.columns.get_loc('SEM_EMA_8') + 1
data.insert(loc=loc_index,column='SEM_Ad_Stock',value=SEM_ad_stock_vector)
loc_index = data.columns.get_loc('Radio_EMA_8') + 1
data.insert(loc=loc_index,column='Radio_Ad_Stock',value=Radio_ad_stock_vector)
loc_index = data.columns.get_loc('Other_EMA_8') + 1
data.insert(loc=loc_index,column='Other_Ad_Stock',value=Other_ad_stock_vector)
loc_index = data.columns.get_loc('Total Investment_EMA_8') + 1
data.insert(loc=loc_index,column='Total_Investment_Ad_Stock',value=Total_Investment_ad_stock_vector)
return data
Assuming the value of Adstock rate(engagement factor)¶
In [110]:
media_investment = calculate_ad_stocks(data=media_investment, engagement_factor=0.6)
media_investment.head(10)
Out[110]:
In [111]:
media_investment.fillna(value=0, inplace=True)
media_investment.head(10)
Out[111]:
In [112]:
media_investment.shape
Out[112]:
NPS Score¶
We will generate weekly data from Year and Month for the nps score data¶
In [113]:
net_promoter_score
Out[113]:
Getting the dataset into suitable format¶
In [114]:
# resetting index
net_promoter_score.reset_index(drop=True, inplace=True)
# Transposing the dataframe
net_promoter_score = net_promoter_score.T
# resetting index
net_promoter_score.reset_index(drop=False, inplace=True)
# renaming columns
net_promoter_score.columns = ['Month', 'NPS', 'Stock Index']
net_promoter_score.drop(net_promoter_score.index[0], inplace=True)
net_promoter_score
Out[114]:
In [115]:
# Temp DataFrame
temp_nps = pd.DataFrame(index=range(total_weeks), columns=['Year', 'Month', 'Week', 'NPS', 'Stock Index'])
temp_nps
Out[115]:
We use the same NPS Score and Stock Index values for each week of a month which is the NPS Score and Stock Index of the entire month
In [116]:
i = 0
for index, row in net_promoter_score.iterrows():
if '15' in row['Month']:
month = re.split("[']", row['Month'])[0][0:3]
for week in dict_name_2015[month]:
temp_nps.iloc[i].Year = 2015
temp_nps.iloc[i].Month = month
temp_nps.iloc[i].Week = week
temp_nps.iloc[i].NPS = row['NPS']
temp_nps.iloc[i]['Stock Index'] = row['Stock Index']
i+=1
elif '16' in row['Month']:
month = re.split("[']", row['Month'])[0][0:3]
for week in dict_name_2016[month]:
temp_nps.iloc[i].Year = 2016
temp_nps.iloc[i].Month = month
temp_nps.iloc[i].Week = week
temp_nps.iloc[i].NPS = row['NPS']
temp_nps.iloc[i]['Stock Index'] = row['Stock Index']
i+=1
temp_nps.head()
Out[116]:
In [117]:
temp_nps['Week'] = temp_nps['Week'].astype('str')
In [118]:
net_promoter_score = temp_nps
net_promoter_score
Out[118]:
In [119]:
net_promoter_score.duplicated('Week').value_counts()
Out[119]:
No duplicates
In [120]:
net_promoter_score.isnull().values.any()
Out[120]:
No Nulls
In [121]:
# Dropping Yeat and Month columns as we won't be needing them anymore
del net_promoter_score['Year']
del net_promoter_score['Month']
In [122]:
net_promoter_score.head()
Out[122]:
In [123]:
nps_original_col = net_promoter_score.columns[1:]
nps_original_col
Out[123]:
Calculating 5-weeks Simple Moving Average for NPS and Stock_Index¶
In [124]:
net_promoter_score = SMA_variables(net_promoter_score,nps_original_col,5)
net_promoter_score.head()
Out[124]:
Calculating 3-weeks Simple Moving Average for NPS and Stock_Index¶
In [125]:
net_promoter_score = SMA_variables(net_promoter_score,nps_original_col,3)
net_promoter_score.head()
Out[125]:
In [126]:
net_promoter_score.fillna(value=0, inplace=True)
net_promoter_score.head()
Out[126]:
Sale Calendar¶
We will generate weekly data from Year and Month for the sale calendar data
In [127]:
sale_calendar
Out[127]:
In [128]:
# Getting the data into required format
sale_calendar.columns = ['Year', 'Sale']
sale_calendar.Year = sale_calendar.Year.apply(lambda x: int(x))
sale_calendar.Sale = sale_calendar.Sale.apply(lambda x: x.replace('th','').strip())
sale_calendar.Sale = sale_calendar.Sale.apply(lambda x: x.replace('rd','').strip())
sale_calendar.Sale = sale_calendar.Sale.apply(lambda x: x.replace('(','').strip())
sale_calendar.Sale = sale_calendar.Sale.apply(lambda x: x.replace(')','').strip())
# Splitting 25-31 Dec into two different rows for the ease of weekly data generation
sale_calendar.iloc[5, sale_calendar.columns.get_loc('Sale')] = '25-31 Dec'
sale_calendar = sale_calendar.append({'Year' : 2016 , 'Sale' : '1-3 Jan'} , ignore_index=True)
sale_calendar.sort_values(by=['Year'], ascending = True, inplace = True)
sale_calendar
Out[128]:
In [129]:
# Dropping '17-15 Oct' entry assuming it is erroneous
sale_calendar.drop(sale_calendar.index[3], inplace=True)
sale_calendar
Out[129]:
In [130]:
# Temp DataFrame
temp_sale_calendar = pd.DataFrame(index=range(total_weeks), columns=['Year', 'Month', 'Week', 'Sale'])
temp_sale_calendar
Out[130]:
%V - ISO 8601 week as a decimal number with Monday as the first day of the week.
We find the number of days in each week of a month on which there was a sale
In [131]:
i = 0
import datetime
for index, row in sale_calendar.iterrows():
week_list = []
month_list = []
year_list = []
date1, date2, month = re.split("[- ]", row.Sale)
month = month[0:3] # Taking only the first 3 character of the month name
year = row.Year
# print("Date => {}-{}-{}".format(date1, month, year))
# print(datetime.datetime.strptime('{}-{}-{}'.format(date1, month, year), "%d-%b-%Y").strftime("%V"))
# print("Date => {}-{}-{}".format(date2, month, year))
# print(datetime.datetime.strptime('{}-{}-{}'.format(date2, month, year), "%d-%b-%Y").strftime("%V"))
date = int(date1)
while date <= int(date2):
# Extracting the week numbers of the range of dates in each month
week = datetime.datetime.strptime('{}-{}-{}'.format(date, month, year), "%d-%b-%Y").strftime("%V")
week_list.append(int(week))
date+=1
week_dict = dict.fromkeys(week_list, 0) # Generating a week list dictionary with default value as 0
for j in week_list:
week_dict[j] = week_dict[j] + 1
for key, value in week_dict.items():
temp_sale_calendar.iloc[i].Year = year
temp_sale_calendar.iloc[i].Month = month
temp_sale_calendar.iloc[i].Week = key
temp_sale_calendar.iloc[i].Sale = value
i+=1
temp_sale_calendar.dropna(axis=0, how='all', inplace=True) # Drop all rows which has all Nulls
temp_sale_calendar.sort_values(by=['Week'], ascending = True, inplace = True)
# Handling the special cases of 53 and 7 week numbers which appear twice
temp_sale_calendar.loc[temp_sale_calendar['Week'] == 53, 'Sale'] = temp_sale_calendar.loc[temp_sale_calendar['Week'] == 53, 'Sale'].sum()
temp_sale_calendar.loc[temp_sale_calendar['Week'] == 7, 'Sale'] = temp_sale_calendar.loc[temp_sale_calendar['Week'] == 7, 'Sale'].sum()
# Dropping the redundant 53 and 7 week numbers
temp_sale_calendar.drop_duplicates(subset=['Week'], keep='first', inplace=True)
temp_sale_calendar.reset_index(drop=True, inplace=True)
temp_sale_calendar.head()
Out[131]:
In [132]:
temp_sale_calendar['Week'] = temp_sale_calendar['Week'].astype('str')
In [133]:
sale_calendar = temp_sale_calendar
sale_calendar.head()
Out[133]:
In [134]:
sale_calendar.duplicated('Week').value_counts()
Out[134]:
No duplicates
In [135]:
sale_calendar.isnull().values.any()
Out[135]:
No Nulls
In [136]:
# Dropping Yeat and Month columns as we won't be needing them anymore
del sale_calendar['Year']
del sale_calendar['Month']
Climate 2015¶
We will generate weekly data from date for the climate data of 2015
In [137]:
# Skipping the first 24 rows while reading the data to ignore the metadata
climate_2015 = pd.read_csv('ONTARIO-2015.csv', skiprows=24)
climate_2015.head()
Out[137]:
In [138]:
climate_2015.columns
Out[138]:
In [139]:
# Dropping the columns with all nulls
climate_2015.dropna(axis=1, thresh=1, inplace=True)
climate_2015.reset_index(drop=True, inplace=True)
climate_2015.head()
Out[139]:
In [140]:
climate_2015.columns
Out[140]:
In [141]:
# Dropping columns with only one distinct value
drop_cols = []
for col in climate_2015.columns:
if 'Flag' in col:
print(climate_2015[col].value_counts())
print()
drop_cols.append(col)
drop_cols.append('Data Quality')
climate_2015.drop(drop_cols, axis=1, inplace=True)
print(climate_2015.columns)
In [142]:
# Formatting column names
climate_2015.columns = [col.replace(' (°C)','').strip() for col in climate_2015.columns]
climate_2015.columns
Out[142]:
In [143]:
climate_2015.info()
In [144]:
# Dropping rows where all the columns have nulls
cols = ['Max Temp',
'Min Temp',
'Mean Temp',
'Heat Deg Days',
'Cool Deg Days',
'Total Rain (mm)',
'Total Snow (cm)',
'Total Precip (mm)',
'Snow on Grnd (cm)']
climate_2015.dropna(subset=cols, inplace=True)
climate_2015.reset_index(drop=True, inplace=True)
climate_2015.info()
In [145]:
# Extracting week # from the date field and formatting it
climate_2015['Week'] = climate_2015['Date/Time'].apply(lambda x: datetime.datetime.strptime(x, "%Y-%m-%d").strftime("%V"))
climate_2015['Week'] = climate_2015['Week'].apply(lambda x: str(x).lstrip('0'))
climate_2015.head()
Out[145]:
In [146]:
# Extracting July to Dec month's data
climate_2015 = climate_2015[climate_2015['Month'] >= 7]
climate_2015.reset_index(drop=True, inplace=True)
climate_2015.head()
Out[146]:
In [147]:
climate_2015.sort_values(by=['Week'], ascending = True, inplace = True)
In [148]:
# Getting weekly data
climate_2015 = climate_2015.groupby(['Week']).agg({'Max Temp':"max", 'Min Temp':"min", 'Mean Temp':"mean", 'Heat Deg Days':"mean", 'Cool Deg Days':"mean", 'Total Rain (mm)':"mean", 'Total Snow (cm)':"mean", 'Total Precip (mm)':"mean", 'Snow on Grnd (cm)':"mean"}).reset_index(drop=False)
climate_2015.head()
Out[148]:
In [149]:
# Dropping 27th week data as it belongs to June 2015
climate_2015.drop(climate_2015[climate_2015['Week'] == '27'].index, inplace = True)
In [150]:
climate_2015.duplicated().any()
Out[150]:
No duplicates
In [151]:
climate_2015.isnull().values.any()
Out[151]:
No Nulls
Climate 2016¶
We will generate weekly data from date for the climate data of 2016
In [152]:
# Skipping the first 24 rows while reading the data to ignore the metadata
climate_2016 = pd.read_csv('ONTARIO-2016.csv', skiprows=24)
climate_2016.head()
Out[152]:
In [153]:
climate_2016.columns
Out[153]:
In [154]:
# Dropping the columns with all nulls
climate_2016.dropna(axis=1, thresh=1, inplace=True)
climate_2016.reset_index(drop=True, inplace=True)
climate_2016.head()
Out[154]:
In [155]:
climate_2016.columns
Out[155]:
In [156]:
# Dropping columns with only one distinct value
drop_cols = []
for col in climate_2016.columns:
if 'Flag' in col:
print(climate_2016[col].value_counts())
print()
drop_cols.append(col)
drop_cols.append('Data Quality')
climate_2016.drop(drop_cols, axis=1, inplace=True)
print(climate_2016.columns)
In [157]:
# Formatting column names
climate_2016.columns = [col.replace(' (°C)','').strip() for col in climate_2016.columns]
climate_2016.columns
Out[157]:
In [158]:
climate_2016.info()
In [159]:
# Dropping rows where all the columns have nulls
cols = ['Max Temp',
'Min Temp',
'Mean Temp',
'Heat Deg Days',
'Cool Deg Days',
'Total Rain (mm)',
'Total Snow (cm)',
'Total Precip (mm)',
'Snow on Grnd (cm)']
climate_2016.dropna(subset=cols, inplace=True)
climate_2016.reset_index(drop=True, inplace=True)
climate_2016.info()
In [160]:
# Extracting week # from the date field and formatting it
climate_2016['Week'] = climate_2016['Date/Time'].apply(lambda x: datetime.datetime.strptime(x, "%Y-%m-%d").strftime("%V"))
climate_2016['Week'] = climate_2016['Week'].apply(lambda x: str(x).lstrip('0'))
climate_2016.head()
Out[160]:
In [161]:
# Extracting Jan to June month's data
climate_2016 = climate_2016[climate_2016['Month'] <= 6]
climate_2016.reset_index(drop=True, inplace=True)
climate_2016.head()
Out[161]:
In [162]:
climate_2016.tail()
Out[162]:
In [163]:
climate_2016.sort_values(by=['Week'], ascending = True, inplace = True)
In [164]:
# Getting weekly data
climate_2016 = climate_2016.groupby(['Week']).agg({'Max Temp':"max", 'Min Temp':"min", 'Mean Temp':"mean", 'Heat Deg Days':"mean", 'Cool Deg Days':"mean", 'Total Rain (mm)':"mean", 'Total Snow (cm)':"mean", 'Total Precip (mm)':"mean", 'Snow on Grnd (cm)':"mean"}).reset_index(drop=False)
climate_2016.head()
Out[164]:
In [165]:
climate_2016.duplicated().any()
Out[165]:
No duplicates
In [166]:
climate_2016.isnull().values.any()
Out[166]:
No Nulls
List Difference¶
In [167]:
def list_diff(list1, list2):
return (list(set(list1) - set(list2)))
Concatenating Climate Datasets¶
In [168]:
print(list_diff(list(climate_2015.columns), list(climate_2016.columns)))
In [169]:
print(climate_2015.shape)
print(climate_2016.shape)
Concatenating the climate data sets to form a single one to be merged with the order data set
In [170]:
climate = pd.concat([climate_2015, climate_2016], axis = 0)
climate.shape
Out[170]:
In [171]:
climate.info()
In [172]:
climate.head()
Out[172]:
In [173]:
climate.Week.unique()
Out[173]:
Extracting 3 separate dataframes for 3 product subcategories - camera accessory, home audio and gaming accessory¶
In [174]:
main_df.shape
Out[174]:
In [175]:
main_df['product_analytic_sub_category'].value_counts()
Out[175]:
In [176]:
cameraaccessory_df = main_df.loc[main_df['product_analytic_sub_category']=='cameraaccessory']
gamingaccessory_df = main_df.loc[main_df['product_analytic_sub_category']=='gamingaccessory']
homeaudio_df = main_df.loc[main_df['product_analytic_sub_category']=='homeaudio']
print('No of rows in cameraaccessory_df: {}'.format(cameraaccessory_df.shape[0]))
print('No of rows in gamingaccessory_df: {}'.format(gamingaccessory_df.shape[0]))
print('No of rows in homeaudio_df: {}'.format(homeaudio_df.shape[0]))
Converting some binary variables to numeric format¶
In [177]:
cameraaccessory_df['is_cod'] = cameraaccessory_df['s1_fact.order_payment_type'].apply(lambda x:1 if x=='cod' else 0)
gamingaccessory_df['is_cod'] = gamingaccessory_df['s1_fact.order_payment_type'].apply(lambda x:1 if x=='cod' else 0)
homeaudio_df['is_cod'] = homeaudio_df['s1_fact.order_payment_type'].apply(lambda x:1 if x=='cod' else 0)
cameraaccessory_df['is_mass_market'] = cameraaccessory_df['product_type'].apply(lambda x:1 if x=='mass_market' else 0)
gamingaccessory_df['is_mass_market'] = gamingaccessory_df['product_type'].apply(lambda x:1 if x=='mass_market' else 0)
homeaudio_df['is_mass_market'] = homeaudio_df['product_type'].apply(lambda x:1 if x=='mass_market' else 0)
cameraaccessory_df.head()
Out[177]:
Deleting the columns 's1_fact.order_payment_type' & 'product_type'
In [178]:
# Dropping Columns which are insignificant to the analysis
drop_columns = ['s1_fact.order_payment_type','product_type']
cameraaccessory_df.drop(drop_columns, axis=1, inplace=True)
gamingaccessory_df.drop(drop_columns, axis=1, inplace=True)
homeaudio_df.drop(drop_columns, axis=1, inplace=True)
For categorical variable(product_analytic_vertical) with multiple levels, creating dummy features (one-hot encoded)¶
In [179]:
# Creating dummy variables for the remaining categorical variable
dummy1 = pd.get_dummies(cameraaccessory_df[['product_analytic_vertical']], prefix='product_vertical', drop_first=True)
dummy2 = pd.get_dummies(gamingaccessory_df[['product_analytic_vertical']], prefix='product_vertical', drop_first=True)
dummy3 = pd.get_dummies(homeaudio_df[['product_analytic_vertical']], prefix='product_vertical', drop_first=True)
# Adding the results to the original dataframes
cameraaccessory_df = pd.concat([cameraaccessory_df, dummy1], axis=1)
gamingaccessory_df = pd.concat([gamingaccessory_df, dummy2], axis=1)
homeaudio_df = pd.concat([homeaudio_df, dummy3], axis=1)
Dropping the repeated variable¶
In [180]:
# removing columns
cameraaccessory_df = cameraaccessory_df.drop('product_analytic_vertical', axis=1)
gamingaccessory_df = gamingaccessory_df.drop('product_analytic_vertical', axis=1)
homeaudio_df = homeaudio_df.drop('product_analytic_vertical', axis=1)
print('Shape of cameraaccessory_df: {}'.format(cameraaccessory_df.shape))
print('Shape of gamingaccessory_df: {}'.format(gamingaccessory_df.shape))
print('Shape of homeaudio_df: {}'.format(homeaudio_df.shape))
Checking the no of unique values in the columns of the 3 new dataframes¶
In [181]:
# Unique value frequencies
unique_values = pd.DataFrame(cameraaccessory_df.apply(lambda x: len(x.value_counts(dropna=False)), axis=0), \
columns=['Unique Value Count']).sort_values(by='Unique Value Count', ascending=True)
unique_values['dtype'] = pd.DataFrame(cameraaccessory_df.dtypes)
unique_values.head()
Out[181]:
In [182]:
# Unique value frequencies
unique_values = pd.DataFrame(gamingaccessory_df.apply(lambda x: len(x.value_counts(dropna=False)), axis=0), \
columns=['Unique Value Count']).sort_values(by='Unique Value Count', ascending=True)
unique_values['dtype'] = pd.DataFrame(gamingaccessory_df.dtypes)
unique_values.head()
Out[182]:
In [183]:
# Unique value frequencies
unique_values = pd.DataFrame(homeaudio_df.apply(lambda x: len(x.value_counts(dropna=False)), axis=0), \
columns=['Unique Value Count']).sort_values(by='Unique Value Count', ascending=True)
unique_values['dtype'] = pd.DataFrame(homeaudio_df.dtypes)
unique_values.head()
Out[183]:
Thus we see that in all 3 dfs, columns 'product_analytic_category' & 'product_analytic_sub_category' have only 1 unique value. Hence we will drop these 2 columns from the 3 dataframes.
In [184]:
# Dropping Columns which are insignificant to the analysis
drop_columns = ['product_analytic_category', 'product_analytic_sub_category']
cameraaccessory_df.drop(drop_columns, axis=1, inplace=True)
gamingaccessory_df.drop(drop_columns, axis=1, inplace=True)
homeaudio_df.drop(drop_columns, axis=1, inplace=True)
Checking if any null values exist in the 3 new dataframes¶
In [185]:
print('Are there any null values in cameraaccessory_df: {}'.format(cameraaccessory_df.isnull().values.any()))
print('Are there any null values in gamingaccessory_df: {}'.format(gamingaccessory_df.isnull().values.any()))
print('Are there any null values in homeaudio_df: {}'.format(homeaudio_df.isnull().values.any()))
Roll Up Order Data to Weekly Level¶
Rolling up order data to get weekly data by taking a mean of all the values per week
For rolling up to obtain weekly data that will be used for modelling, we will ignore list_price and product_mrp. These features are closely related to gmv(revenue). However they dont add any business value. It is obvious that having a high mpr or list_price will increase the revenue. But that wont be a good business decision to maximise revenue.¶
Units also is not a very important feature from business perspective as its obvious that higher the no of units sold, more will be the revenue. The challenge of prediction is to find attributes, tuning which, customers will buy more product units, thereby increasing revenue.¶
In [186]:
cameraaccessory_df = cameraaccessory_df.groupby(['Week']).agg({'gmv':"sum",'Discount%':'mean','deliverybdays': "mean", \
'deliverycdays':'mean','sla':'mean', 'product_procurement_sla':'mean', \
'payday_flag':'sum','occassion_flag':'sum','is_cod':'sum', 'is_mass_market':'sum', \
'product_vertical_cameraaccessory':'sum', \
'product_vertical_camerabag':'sum', 'product_vertical_camerabattery':'sum', \
'product_vertical_camerabatterycharger':'sum', 'product_vertical_camerabatterygrip': \
'sum','product_vertical_cameraeyecup':'sum','product_vertical_camerafilmrolls':'sum', \
'product_vertical_camerahousing':'sum','product_vertical_cameraledlight':'sum', \
'product_vertical_cameramicrophone':'sum','product_vertical_cameramount':'sum', \
'product_vertical_cameraremotecontrol':'sum', 'product_vertical_cameratripod':'sum', \
'product_vertical_extensiontube':'sum', 'product_vertical_filter':'sum', \
'product_vertical_flash':'sum','product_vertical_flashshoeadapter':'sum', \
'product_vertical_lens':'sum','product_vertical_reflectorumbrella':'sum', \
'product_vertical_softbox':'sum','product_vertical_strap':'sum', \
'product_vertical_teleconverter':'sum','product_vertical_telescope':'sum'}) \
.reset_index(drop=False)
cameraaccessory_df.shape
Out[186]:
In [187]:
gamingaccessory_df = gamingaccessory_df.groupby(['Week']).agg({'gmv':"sum",'Discount%':'mean','deliverybdays':"mean", \
'deliverycdays':'mean','sla':'mean','product_procurement_sla': \
'mean','payday_flag':'sum','occassion_flag':'sum','is_cod':'sum', \
'is_mass_market':'sum','product_vertical_gamecontrolmount':'sum', \
'product_vertical_gamepad':'sum', \
'product_vertical_gamingaccessorykit':'sum', \
'product_vertical_gamingadapter':'sum', \
'product_vertical_gamingchargingstation':'sum', \
'product_vertical_gamingheadset':'sum', \
'product_vertical_gamingkeyboard':'sum', \
'product_vertical_gamingmemorycard':'sum', \
'product_vertical_gamingmouse':'sum', \
'product_vertical_gamingmousepad':'sum', \
'product_vertical_gamingmousepad':'sum', \
'product_vertical_gamingspeaker':'sum',
'product_vertical_joystickgamingwheel':'sum', \
'product_vertical_motioncontroller':'sum', \
'product_vertical_tvoutcableaccessory':'sum'}) \
.reset_index(drop=False)
gamingaccessory_df.shape
Out[187]:
In [188]:
homeaudio_df = homeaudio_df.groupby(['Week']).agg({'gmv':"sum",'Discount%':'mean','deliverybdays': \
'mean','deliverycdays':'mean','sla':'mean','product_procurement_sla':'mean',\
'payday_flag':'sum','occassion_flag':'sum','is_cod':'sum', \
'is_mass_market':'sum','product_vertical_djcontroller':'sum', \
'product_vertical_dock':'sum', 'product_vertical_dockingstation':'sum', \
'product_vertical_fmradio':'sum', 'product_vertical_hifisystem':'sum', \
'product_vertical_homeaudiospeaker':'sum', \
'product_vertical_karaokeplayer':'sum', 'product_vertical_slingbox':'sum', \
'product_vertical_soundmixer':'sum','product_vertical_voicerecorder':'sum'})\
.reset_index(drop=False)
homeaudio_df.shape
Out[188]:
Calculating Payday week & Holiday week¶
- If Payday falls within the week(payday_flag > 0), then payday week = 1, else 0
- If holiday falls within the week(occassion_flag > 0), then holiday week = 1, else 0
In [189]:
cameraaccessory_df['payday_week'] = cameraaccessory_df['payday_flag'].apply(lambda x:1 if x > 0 else 0)
gamingaccessory_df['payday_week'] = gamingaccessory_df['payday_flag'].apply(lambda x:1 if x > 0 else 0)
homeaudio_df['payday_week'] = homeaudio_df['payday_flag'].apply(lambda x:1 if x > 0 else 0)
cameraaccessory_df['holiday_week'] = cameraaccessory_df['occassion_flag'].apply(lambda x:1 if x > 0 else 0)
gamingaccessory_df['holiday_week'] = gamingaccessory_df['occassion_flag'].apply(lambda x:1 if x > 0 else 0)
homeaudio_df['holiday_week'] = homeaudio_df['occassion_flag'].apply(lambda x:1 if x > 0 else 0)
cameraaccessory_df.head()
Out[189]:
Dropping columns 'payday_flag' and 'occassion_flag'
In [190]:
drop_columns = ['payday_flag','occassion_flag']
cameraaccessory_df = cameraaccessory_df.drop(drop_columns, axis=1)
gamingaccessory_df = gamingaccessory_df.drop(drop_columns, axis=1)
homeaudio_df = homeaudio_df.drop(drop_columns, axis=1)
cameraaccessory_df.head()
Out[190]:
In [191]:
print('Shape of cameraaccessory_df: {}'.format(cameraaccessory_df.shape))
print('Shape of gamingaccessory_df: {}'.format(gamingaccessory_df.shape))
print('Shape of homeaudio_df: {}'.format(homeaudio_df.shape))
Merge DataFrames¶
Pre-merge activity¶
In [192]:
print(cameraaccessory_df.Week.unique())
print("--------------------------------------------------------")
print(gamingaccessory_df.Week.unique())
print("--------------------------------------------------------")
print(homeaudio_df.Week.unique())
print("--------------------------------------------------------")
In [193]:
# Formatting pre merge
cameraaccessory_df.Week = cameraaccessory_df.Week.apply(lambda x: x.lstrip('0'))
gamingaccessory_df.Week = gamingaccessory_df.Week.apply(lambda x: x.lstrip('0'))
homeaudio_df.Week = homeaudio_df.Week.apply(lambda x: x.lstrip('0'))
Merging Media Investment Dataset¶
In [194]:
# Taking an left join on Week column
cameraaccessory_df = pd.merge(cameraaccessory_df, media_investment, how='left', on='Week')
gamingaccessory_df = pd.merge(gamingaccessory_df, media_investment, how='left', on='Week')
homeaudio_df = pd.merge(homeaudio_df, media_investment, how='left', on='Week')
print('Are there any null values in cameraaccessory_df: {}'.format(cameraaccessory_df.isnull().values.any()))
print('Are there any null values in gamingaccessory_df: {}'.format(gamingaccessory_df.isnull().values.any()))
print('Are there any null values in homeaudio_df: {}'.format(homeaudio_df.isnull().values.any()))
In [195]:
print('Shape of cameraaccessory_df: {}'.format(cameraaccessory_df.shape))
print('Shape of gamingaccessory_df: {}'.format(gamingaccessory_df.shape))
print('Shape of homeaudio_df: {}'.format(homeaudio_df.shape))
Merging Net Promoter Score Data Set¶
In [196]:
# Taking an left join on Week column
cameraaccessory_df = pd.merge(cameraaccessory_df, net_promoter_score, how='left', on='Week')
gamingaccessory_df = pd.merge(gamingaccessory_df, net_promoter_score, how='left', on='Week')
homeaudio_df = pd.merge(homeaudio_df, net_promoter_score, how='left', on='Week')
print('Are there any null values in cameraaccessory_df: {}'.format(cameraaccessory_df.isnull().values.any()))
print('Are there any null values in gamingaccessory_df: {}'.format(gamingaccessory_df.isnull().values.any()))
print('Are there any null values in homeaudio_df: {}'.format(homeaudio_df.isnull().values.any()))
In [197]:
print('Shape of cameraaccessory_df: {}'.format(cameraaccessory_df.shape))
print('Shape of gamingaccessory_df: {}'.format(gamingaccessory_df.shape))
print('Shape of homeaudio_df: {}'.format(homeaudio_df.shape))
Merging Climate Data Set¶
In [198]:
# Taking an left join on Week column
cameraaccessory_df = pd.merge(cameraaccessory_df, climate, how='left', on='Week')
gamingaccessory_df = pd.merge(gamingaccessory_df, climate, how='left', on='Week')
homeaudio_df = pd.merge(homeaudio_df, climate, how='left', on='Week')
print('Are there any null values in cameraaccessory_df: {}'.format(cameraaccessory_df.isnull().values.any()))
print('Are there any null values in gamingaccessory_df: {}'.format(gamingaccessory_df.isnull().values.any()))
print('Are there any null values in homeaudio_df: {}'.format(homeaudio_df.isnull().values.any()))
As expected, there are nulls in the climate columns now for the week# 3 which doesn't exist in the climate dataset
In [199]:
print('No of rows with null values in cameraaccessory_df:{}'.format(cameraaccessory_df[cameraaccessory_df.isnull().any(axis=1)].shape))
print('No of rows with null values in gamingaccessory_df:{}'.format(gamingaccessory_df[gamingaccessory_df.isnull().any(axis=1)].shape))
print('No of rows with null values in homeaudio_df:{}'.format(homeaudio_df[homeaudio_df.isnull().any(axis=1)].shape))
Since the number of rows with nulls is only 1, we will drop them
In [200]:
cols = ['Max Temp', 'Min Temp', 'Mean Temp', 'Heat Deg Days', 'Cool Deg Days', 'Total Rain (mm)', 'Total Snow (cm)', 'Total Precip (mm)', 'Snow on Grnd (cm)']
cameraaccessory_df.dropna(subset=cols, inplace=True)
gamingaccessory_df.dropna(subset=cols, inplace=True)
homeaudio_df.dropna(subset=cols, inplace=True)
In [201]:
print('Are there any null values in cameraaccessory_df: {}'.format(cameraaccessory_df.isnull().values.any()))
print('Are there any null values in gamingaccessory_df: {}'.format(gamingaccessory_df.isnull().values.any()))
print('Are there any null values in homeaudio_df: {}'.format(homeaudio_df.isnull().values.any()))
No Nulls
In [202]:
print('Shape of cameraaccessory_df: {}'.format(cameraaccessory_df.shape))
print('Shape of gamingaccessory_df: {}'.format(gamingaccessory_df.shape))
print('Shape of homeaudio_df: {}'.format(homeaudio_df.shape))
Merging Sale Calendar Data Set¶
In [203]:
# Taking an left join on Week column
cameraaccessory_df = pd.merge(cameraaccessory_df, sale_calendar, how='left', on='Week')
gamingaccessory_df = pd.merge(gamingaccessory_df, sale_calendar, how='left', on='Week')
homeaudio_df = pd.merge(homeaudio_df, sale_calendar, how='left', on='Week')
print('Are there any null values in cameraaccessory_df: {}'.format(cameraaccessory_df.isnull().values.any()))
print('Are there any null values in gamingaccessory_df: {}'.format(gamingaccessory_df.isnull().values.any()))
print('Are there any null values in homeaudio_df: {}'.format(homeaudio_df.isnull().values.any()))
In [204]:
# Imputing the nulls with 0 meaning for those weeks there was 0 days in special sale
cameraaccessory_df['Sale'].fillna(value=0, inplace=True)
gamingaccessory_df['Sale'].fillna(value=0, inplace=True)
homeaudio_df['Sale'].fillna(value=0, inplace=True)
In [205]:
print('Are there any null values in cameraaccessory_df: {}'.format(cameraaccessory_df.isnull().values.any()))
print('Are there any null values in gamingaccessory_df: {}'.format(gamingaccessory_df.isnull().values.any()))
print('Are there any null values in homeaudio_df: {}'.format(homeaudio_df.isnull().values.any()))
No Nulls
In [206]:
print('Shape of cameraaccessory_df: {}'.format(cameraaccessory_df.shape))
print('Shape of gamingaccessory_df: {}'.format(gamingaccessory_df.shape))
print('Shape of homeaudio_df: {}'.format(homeaudio_df.shape))
In [207]:
cameraaccessory_df.head()
Out[207]:
Sorting the dataframes based on Week# from July,2015 to June,2016¶
In [208]:
# String to Int
cameraaccessory_df['Week'] = cameraaccessory_df['Week'].astype('int64')
gamingaccessory_df['Week'] = gamingaccessory_df['Week'].astype('int64')
homeaudio_df['Week'] = homeaudio_df['Week'].astype('int64')
In [209]:
cameraaccessory_df1 = cameraaccessory_df.loc[cameraaccessory_df['Week'] >= 28]
cameraaccessory_df2 = cameraaccessory_df.loc[cameraaccessory_df['Week'] < 28]
gamingaccessory_df1 = gamingaccessory_df.loc[gamingaccessory_df['Week'] >= 28]
gamingaccessory_df2 = gamingaccessory_df.loc[gamingaccessory_df['Week'] < 28]
homeaudio_df1 = homeaudio_df.loc[homeaudio_df['Week'] >= 28]
homeaudio_df2 = homeaudio_df.loc[homeaudio_df['Week'] < 28]
cameraaccessory_df = cameraaccessory_df1.append(cameraaccessory_df2)
gamingaccessory_df = gamingaccessory_df1.append(gamingaccessory_df2)
homeaudio_df = homeaudio_df1.append(homeaudio_df2)
gamingaccessory_df.head()
Out[209]:
In [210]:
cameraaccessory_df.fillna(value=0, inplace=True)
gamingaccessory_df.fillna(value=0, inplace=True)
homeaudio_df.fillna(value=0, inplace=True)
cameraaccessory_df.head()
Out[210]:
In [211]:
print('Shape of cameraaccessory_df: {}'.format(cameraaccessory_df.shape))
print('Shape of gamingaccessory_df: {}'.format(gamingaccessory_df.shape))
print('Shape of homeaudio_df: {}'.format(homeaudio_df.shape))
EDA & Visualization¶
In [212]:
main_df.shape
Out[212]:
Creating a dataframe with required 3 product sub categories from the main dataframe¶
In [213]:
product_sub = main_df.loc[(main_df['product_analytic_sub_category'] == 'cameraaccessory')| \
(main_df['product_analytic_sub_category'] == 'gamingaccessory')| \
(main_df['product_analytic_sub_category'] == 'homeaudio')]
product_sub.shape
Out[213]:
Comparing Distribution of Discount% for 2 product types - Luxury and Mass Market¶
In [214]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(3, 3), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
sns.boxplot(x='product_type', y='Discount%', palette='coolwarm', data=product_sub)
# plot legend
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The median discount percentage offered for luxury items is less compared to that of Mass Market Products.¶
This is a known trend among luxury products or luxury brands to offer limited or no discounts to retain the exclusivity of their products.
Displaying trend of NPS and Stock Index by week¶
In [215]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 4), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
# subplot 1
plt.subplot(2, 1, 1)
plt.plot(net_promoter_score.iloc[:,0], net_promoter_score.iloc[:,1], 'g-', linewidth=2)
plt.xlabel('Week', fontsize=10);
plt.ylabel('NPS', fontsize=10);
# subplot 2
plt.subplot(2, 1, 2)
plt.plot(net_promoter_score.iloc[:,0], net_promoter_score.iloc[:,4], 'r-', linewidth=2)
plt.xlabel('Week', fontsize=10);
plt.ylabel('Stock Index', fontsize=10);
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
Consumer NPS score is highest in weeks 32 – 35 , which coincides with the time when maximum discounts were being offered.
Displaying trend of various Media Channel Investments by week¶
In [218]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(8, 5), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
# subplot 1
plt.subplot(1, 1, 1)
plt.plot(media_investment.iloc[:,0], media_investment.iloc[:,1::5], linewidth=3, alpha = 0.7)
plt.xlabel('Week#', fontsize=10)
plt.ylabel('Media Channel Investments', fontsize=10)
plt.legend(media_investment.iloc[:,1::5])
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
Over the past year, bulk of the Ad Investment has been made in Sponsorships followed by Online Marketing & Search Engine Marketing(specially during Thanksgiving).
Average Revenue from Holiday/Non-holiday days for the 3 product subcategories¶
In [219]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(6,4), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
sns.barplot(y='gmv', x='product_analytic_sub_category', hue ='occassion_flag', \
palette='husl', data=product_sub, estimator=np.median)
# plot legend
plt.legend(frameon=True, fontsize='small', shadow='True', title='Is a Holiday? 0-No 1-Yes', bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The average(median) revenue for 3 product sub categories from holiday and non-holiday days are more or less comparable.¶
No of items(Luxury/Mass-market) sold per 3 product subcategories¶
In [220]:
product_sub.groupby(["product_type", "product_analytic_sub_category"]).size().unstack().plot(kind='bar', \
stacked=True, figsize=(8,6), \
fontsize = 10)
# plot x axis label
plt.xlabel('Product Type', fontsize = 12)
# plot y axis label
plt.ylabel('No of Items Sold', fontsize = 12)
# plot legend
plt.legend(frameon=True, fontsize='small', shadow='True', title='Product Sub-category', \
bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
From the above graph, we have the following observations:¶
- Most of the units sold belonged to the mass market category.
- Among mass market products sold, Camera and Gaming Accessories related products were sold the most.
- Home Audio products were the most popular among the luxury products sold
Total items sold per 3 product subcategories per Month¶
In [221]:
product_sub.groupby(["Month", "product_analytic_sub_category"]).size().unstack().plot(kind='bar', \
stacked=True, figsize=(8,6), \
fontsize = 10)
# plot x axis label
plt.xlabel('Month', fontsize = 12)
# plot y axis label
plt.ylabel('No of Items Sold', fontsize = 12)
# plot legend
plt.legend(frameon=True, fontsize='small', shadow='True', title='Product Sub-category', \
bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
Total items sold per 3 product subcategories per Week¶
In [222]:
product_sub.groupby(["Week", "product_analytic_sub_category"]).size().unstack().plot(kind='bar', \
stacked=True, figsize=(15,6), \
fontsize = 10)
# plot x axis label
plt.xlabel('Week', fontsize = 12)
# plot y axis label
plt.ylabel('No of Items Sold', fontsize = 12)
# plot legend
plt.legend(frameon=True, fontsize='small', shadow='True', title='Product Sub-category', \
bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The sale on the 42nd week (Thanksgiving week) is maximum. Overall, October has seen most no of items being sold.¶
Top 10 Product Verticals which brought the Maximum Revenue for 3 product sub-categories¶
In [223]:
highest_gmv = pd.DataFrame(product_sub.groupby(['product_analytic_sub_category','product_analytic_vertical'])['gmv'].sum().sort_values(ascending=False).reset_index()).head(10)
highest_gmv['product_in_category'] = highest_gmv[['product_analytic_vertical','product_analytic_sub_category']].apply(lambda x: ' in '.join(x), axis=1)
highest_gmv.head(20)
Out[223]:
In [224]:
import squarify # pip install squarify (algorithm for treemap)
# Create a dataset:
my_values=list(highest_gmv['gmv'])
plt.figure(figsize=(12,6), dpi=80, facecolor='w', edgecolor='k')
# create a color palette, mapped to these values
cmap = matplotlib.cm.Blues
mini=min(my_values)
maxi=max(my_values)
norm = matplotlib.colors.Normalize(vmin=mini, vmax=maxi)
colors = [cmap(norm(value)) for value in my_values]
# Change color
squarify.plot(sizes=my_values, alpha=.8, label=highest_gmv['product_in_category'],color=colors)
plt.axis('off')
# Show graphic
plt.tight_layout()
plt.show()
homeaudiospeaker in homeaudio brought the largest revenue followed by lens in cameraaccessory & gamepad in gamingaccessory.¶
Top 10 Product Verticals with most no of sales for 3 product sub-categories¶
In [225]:
most_sales = pd.DataFrame(product_sub.groupby(['product_analytic_sub_category','product_analytic_vertical'])['units'].count().sort_values(ascending=False).reset_index()).head(10)
most_sales['product_in_category'] = most_sales[['product_analytic_vertical','product_analytic_sub_category']].apply(lambda x: ' in '.join(x), axis=1)
most_sales.head(20)
Out[225]:
In [226]:
import squarify # pip install squarify (algorithm for treemap)
# Create a dataset:
my_values=list(most_sales['units'])
plt.figure(figsize=(12,6), dpi=80, facecolor='w', edgecolor='k')
# create a color palette, mapped to these values
cmap = matplotlib.cm.Blues
mini=min(my_values)
maxi=max(my_values)
norm = matplotlib.colors.Normalize(vmin=mini, vmax=maxi)
colors = [cmap(norm(value)) for value in my_values]
# Change color
squarify.plot(sizes=my_values, alpha=.8, label=most_sales['product_in_category'],color=colors)
plt.axis('off')
# Show graphic
plt.tight_layout()
plt.show()
homeaudiospeaker in homeaudio had the most no of sales followed by gamingheadset & gamepad in gamingaccessory.¶
Comparison of Trends of Revenue Discount% & Total Media Investment over the weeks¶
In [227]:
cameraaccessory = cameraaccessory_df.copy()
gamingaccessory = gamingaccessory_df.copy()
homeaudio = homeaudio_df.copy()
cameraaccessory['Week'] = cameraaccessory['Week'].apply(str)
gamingaccessory['Week'] = gamingaccessory['Week'].apply(str)
homeaudio['Week'] = homeaudio['Week'].apply(str)
In [228]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 8), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
# subplot 1
plt.subplot(2, 1, 1)
plt.plot(gamingaccessory.iloc[:,0], gamingaccessory.iloc[:,1], 'b-', linewidth=2, alpha=0.7)
plt.plot(cameraaccessory.iloc[:,0], cameraaccessory.iloc[:,1], 'r-', linewidth=2, alpha=0.7)
plt.plot(homeaudio.iloc[:,0], homeaudio.iloc[:,1], 'g-', linewidth=2, alpha=0.7)
plt.xlabel('Week', fontsize=10);
plt.ylabel('Total GMV', fontsize=10);
plt.legend(['gamingaccessory gmv','cameraaccessory gmv','homeaudio gmv'])
# subplot 2
plt.subplot(2, 1, 2)
plt.plot(gamingaccessory.iloc[:,0], gamingaccessory.iloc[:,2], 'b-', linewidth=2, alpha=0.7)
plt.plot(cameraaccessory.iloc[:,0], cameraaccessory.iloc[:,2], 'r-', linewidth=2, alpha=0.7)
plt.plot(homeaudio.iloc[:,0], homeaudio.iloc[:,2], 'g-', linewidth=2, alpha=0.7)
plt.plot(homeaudio.iloc[:,0], homeaudio.iloc[:,21], 'c-', linewidth=2, alpha=0.7)
plt.xlabel('Week', fontsize=10);
plt.ylabel('Promotion & Ads', fontsize=10);
plt.legend(['gamingaccessory disount','cameraaccessory disount','homeaudio disount','total ad investment'])
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The following observations were noted from the above time series plots:¶
For the week# 42 (during
Thanksgiving), all the graphs show a steep rise. Revenue increased becuase of both higher discount% and increased Ad Investment.For the week 32(August), Revenue generated was the lowest from all 3 product subcategories. This can be observed as a direct relation to minimum amount of total investment in Ads. Discount was also lowest for all products apart from camera accessories. Post this dip in revenue, discount% was increased to bring about higher sales. This increase in Discount% was observed most in the case of gaming accessories. However, barring home audio products, the revenue from other products was seen to be constant for the next 3 weeks after which, the revenue started to pick up.
In general the average disount% offered for home audio products is lesser compared to that of the other product subcategories.
Analyzing how Sales Amount and Revenue vary based on Discount%¶
In [229]:
# Segmenting the Discount% into various bins
def discount_binning(df,cut_points,label_names):
column_index = df.columns.get_loc('Discount%') + 1
df.insert(loc=column_index,column='Discount Bins',value=pd.cut(df['Discount%'],cut_points,labels=label_names, include_lowest=True))
return df
cut_points = [0,10,20,30,40,50,60,70,80,90,100]
label_names = ["Below 10%","Between 10-20%","Between 20-30%","Between 30-40%","Between 40-50%","Between 50-60%", \
"Between 60-70%","Between 70-80%","Between 80-90%","Between 90-100%"]
product_sub = discount_binning(product_sub,cut_points,label_names)
product_sub.head()
Out[229]:
In [230]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10,4), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
# subplot 1
plt.subplot(1, 2, 1)
sns.barplot(x='gmv', y='Discount Bins', palette='husl', data=product_sub, estimator=np.median)
# subplot 2
plt.subplot(1, 2, 2)
sns.countplot(y='Discount Bins', palette='husl', data=product_sub)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
Median Revenue is maximum when Average discount% is between 10-20%. But beyond that, average revenue slowly starts to decline.
The sales on the other hand shows a steady increase with increase in Discount percentage till it peaks at 50-60% after which it starts to fall again.
This shows that at higher discount, although the sales are good, the revenue collapses signifying a loss for the company. An average discount of 10-20% is the most profitable for the company.¶
Percentage of items sold at different Discount% segments¶
In [231]:
from collections import Counter
labels, values = zip(*Counter(product_sub["Discount Bins"]).items())
colors = ["#ff9999", "#99d8c9", "#2ca25f", "#8856a7","#43a2ca","#fdbb84","#e34a33","#bdbdbd","#636363","#f7fcb9"]
piechart_df = (pd.DataFrame(list(values),list(labels)))
piechart_df = piechart_df.reset_index()
fig = plt.figure(figsize=[6,6])
plt.pie(piechart_df[0],labels=piechart_df["index"],startangle=180,explode=(0,0,0,0.1,0,0,0,0,0,0),autopct="%1.1f%%", \
shadow=True, colors=colors)
plt.tight_layout()
plt.title("No of Items sold at Different Discount%", fontsize=15)
plt.show()
Most of the sales take place when Discount% is between 50-60%.¶
Analyzing how Sales Amount and Revenue vary based on Payment Types¶
In [232]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10,4), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
# subplot 1
plt.subplot(1, 2, 1)
sns.barplot(x='gmv', y='product_analytic_sub_category', hue ='s1_fact.order_payment_type', \
palette='coolwarm', data=product_sub, estimator=np.median)
# subplot 2
plt.subplot(1, 2, 2)
sns.countplot(y='product_analytic_sub_category', hue ='s1_fact.order_payment_type', palette='coolwarm', data=product_sub)
# plot legend
plt.legend(frameon=True, fontsize='small', shadow='True', title='Payment Type', bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
Except for Home Audio Products, for the other 2 product sub categories, we observe that the median Revenue from Prepaid orders is more than that from COD products even though the no of products sold is way higher in case of COD products for all categories.¶
Finding the percentage of Luxury & Mass-market Products from 3 sub-categories¶
In [233]:
product_sub['luxury'] = product_sub['product_type'].apply(lambda x:1 if x=='luxury' else 0)
product_sub['mass_market'] = product_sub['product_type'].apply(lambda x:1 if x=='mass_market' else 0)
product_sub.head()
Out[233]:
In [234]:
product_type = pd.DataFrame(product_sub.groupby('product_analytic_sub_category')['luxury','mass_market'].sum().reset_index())
product_type
Out[234]:
In [235]:
# From raw value to percentage
r = [0,1,2]
totals = [i+j for i,j in zip(product_type['luxury'], product_type['mass_market'])]
luxury = [i / j * 100 for i,j in zip(product_type['luxury'], totals)]
mass_market = [i / j * 100 for i,j in zip(product_type['mass_market'], totals)]
names = list(product_type['product_analytic_sub_category'])
# plot
# adjust figure size
plt.figure(figsize=(6,6), dpi=80, facecolor='w', edgecolor='k')
barWidth = 0.85
# Create Luxury Bars
plt.bar(r, luxury, color='#b5ffb9', edgecolor='white', width=barWidth)
# Create mass_market Bars
plt.bar(r, mass_market, bottom=[i for i in luxury], color='#a3acff', edgecolor='white', width=barWidth)
# Custom x axis
plt.xticks(r, names, rotation='vertical')
plt.legend(['luxury','mass_market'],frameon=True, fontsize='small', shadow='True', title='Product Type', bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)
plt.title("Percentage of Luxury & Mass-market Product for different Sub-categories")
# Show graphic
plt.tight_layout()
plt.show()
Percentage of luxury products under HomeAudio is much more compared to the other sub categories.¶
Finding the percentage of COD & Prepaid Products from 3 sub-categories¶
In [236]:
product_sub['prepaid'] = product_sub['s1_fact.order_payment_type'].apply(lambda x:1 if x=='prepaid' else 0)
product_sub['cod'] = product_sub['s1_fact.order_payment_type'].apply(lambda x:1 if x=='cod' else 0)
product_sub.head()
Out[236]:
In [237]:
payment_type = pd.DataFrame(product_sub.groupby('product_analytic_sub_category')['prepaid','cod'].sum().reset_index())
payment_type
Out[237]:
In [238]:
# From raw value to percentage
r = [0,1,2]
totals = [i+j for i,j in zip(payment_type['prepaid'], payment_type['cod'])]
prepaid = [i / j * 100 for i,j in zip(payment_type['prepaid'], totals)]
cod = [i / j * 100 for i,j in zip(payment_type['cod'], totals)]
names = list(payment_type['product_analytic_sub_category'])
# plot
# adjust figure size
plt.figure(figsize=(6,6), dpi=80, facecolor='w', edgecolor='k')
barWidth = 0.85
# Create prepaid Bars
plt.bar(r, prepaid, color='#b5ffb9', edgecolor='white', width=barWidth)
# Create cod Bars
plt.bar(r, cod, bottom=[i for i in prepaid], color='#a3acff', edgecolor='white', width=barWidth)
# Custom x axis
plt.xticks(r, names, rotation='vertical')
plt.legend(['prepaid','cod'],frameon=True, fontsize='small', shadow='True', title='Payment Type', bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)
plt.title("Percentage of Prepaid & COD Products for different Sub-categories")
# Show graphic
plt.tight_layout()
plt.show()
Percentage of prepaid payments under Camera Accessory was observed to be slightly more compared to that of the other sub categories.¶
Finding the percentage of Luxury and Mass_market Products under different Discount groups¶
In [239]:
product_type_with_discount = pd.DataFrame(product_sub.groupby('Discount Bins')['luxury','mass_market'].sum().reset_index())
product_type_with_discount
Out[239]:
In [240]:
# From raw value to percentage
r = [0,1,2,3,4,5,6,7,8,9]
totals = [i+j for i,j in zip(product_type_with_discount['luxury'], product_type_with_discount['mass_market'])]
luxury = [i / j * 100 for i,j in zip(product_type_with_discount['luxury'], totals)]
mass_market = [i / j * 100 for i,j in zip(product_type_with_discount['mass_market'], totals)]
names = list(product_type_with_discount['Discount Bins'])
# plot
# adjust figure size
plt.figure(figsize=(10,6), dpi=80, facecolor='w', edgecolor='k')
barWidth = 0.85
# Create Luxury Bars
plt.bar(r, luxury, color='#b5ffb9', edgecolor='white', width=barWidth)
# Create mass_market Bars
plt.bar(r, mass_market, bottom=[i for i in luxury], color='#a3acff', edgecolor='white', width=barWidth)
# Custom x axis
plt.xticks(r, names, rotation='vertical')
plt.legend(['luxury','mass_market'],frameon=True, fontsize='small', shadow='True', title='Product Type', bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)
plt.title("Percentage of Luxury & Mass-market Product under different Discount Group")
# Show graphic
plt.tight_layout()
plt.show()
Percentage of luxury products were given a discount between 10-20%.¶
Relationship between Revenue and Advertisement Spends¶
In [241]:
# Slightly alter the figure size to make it more horizontal.
#plt.figure(figsize=(10,4), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
sns.pairplot(cameraaccessory, x_vars=['Total Investment', 'TV', 'Digital', 'Sponsorship', 'Content Marketing', \
'Online marketing', 'Affiliates', 'SEM', 'Radio', 'Other'], \
y_vars='gmv',size=4, aspect=0.5, kind='reg')
plt.title('cameraaccessory', fontsize = 20)
sns.pairplot(gamingaccessory, x_vars=['Total Investment', 'TV', 'Digital', 'Sponsorship', 'Content Marketing', \
'Online marketing', 'Affiliates', 'SEM', 'Radio', 'Other'], \
y_vars='gmv',size=4, aspect=0.5, kind='reg')
plt.title('gamingaccessory', fontsize = 20)
sns.pairplot(homeaudio, x_vars=['Total Investment', 'TV', 'Digital', 'Sponsorship', 'Content Marketing', \
'Online marketing', 'Affiliates', 'SEM', 'Radio', 'Other'], \
y_vars='gmv',size=4, aspect=0.5, kind='reg')
plt.title('homeaudio', fontsize = 20)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
In [243]:
rev_ad_columns = ['gmv','Total Investment', 'Total Investment_SMA_3', 'Total Investment_SMA_5', 'Total Investment_EMA_8', \
'Total_Investment_Ad_Stock', 'TV', 'TV_SMA_3', 'TV_SMA_5', 'TV_EMA_8', 'TV_Ad_Stock', 'Digital', \
'Digital_SMA_3', 'Digital_SMA_5', 'Digital_EMA_8', 'Digital_Ad_Stock', 'Sponsorship', 'Sponsorship_SMA_3', \
'Sponsorship_SMA_5', 'Sponsorship_EMA_8', 'Sponsorship_Ad_Stock', 'Content Marketing', \
'Content Marketing_SMA_3','Content Marketing_SMA_5','Content Marketing_EMA_8', 'Content_Marketing_Ad_Stock', \
'Online marketing', 'Online marketing_SMA_3', 'Online marketing_SMA_5', 'Online marketing_EMA_8', \
'Online_marketing_Ad_Stock', 'Affiliates', 'Affiliates_SMA_3', 'Affiliates_SMA_5', 'Affiliates_EMA_8', \
'Affiliates_Ad_Stock', 'SEM', 'SEM_SMA_3', 'SEM_SMA_5', 'SEM_EMA_8', 'SEM_Ad_Stock', 'Radio', 'Radio_SMA_3', \
'Radio_SMA_5', 'Radio_EMA_8', 'Radio_Ad_Stock', 'Other', 'Other_SMA_3', 'Other_SMA_5', 'Other_EMA_8', \
'Other_Ad_Stock']
In [244]:
cameraaccessory_ad = cameraaccessory[rev_ad_columns]
gamingaccessory_ad = gamingaccessory[rev_ad_columns]
homeaudio_ad = homeaudio[rev_ad_columns]
homeaudio_ad.head()
Out[244]:
Relationship between Revenue and Various Ad Spends for Camera Accessories¶
In [245]:
plt.figure(figsize=(20,10), dpi=80, facecolor='w', edgecolor='k', frameon='True')
cam_cor = cameraaccessory_ad.corr()
sns.heatmap(cam_cor, cmap="YlGnBu", annot=True)
plt.show()
Relationship between Revenue and Various Ad Spends for Gaming Accessories¶
In [246]:
plt.figure(figsize=(20,10), dpi=80, facecolor='w', edgecolor='k', frameon='True')
gam_cor = gamingaccessory_ad.corr()
sns.heatmap(gam_cor, cmap="YlGnBu", annot=True)
plt.show()
Relationship between Revenue and Various Ad Spends for Home Audio¶
In [247]:
plt.figure(figsize=(20,10), dpi=80, facecolor='w', edgecolor='k', frameon='True')
ha_cor = homeaudio_ad.corr()
sns.heatmap(ha_cor, cmap="YlGnBu", annot=True)
plt.show()
Building Linear Regression Models¶
In [248]:
# making a copy of original dataframes
cameraaccessory_org_df = cameraaccessory_df.copy()
gamingaccessory_org_df = gamingaccessory_df.copy()
homeaudio_org_df = homeaudio_df.copy()
homeaudio_org_df.head()
Out[248]:
In [249]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(gamingaccessory_org_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(gamingaccessory_org_df.isnull().sum()/gamingaccessory_org_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[249]:
Rescaling the Features of the 3 Dataframes¶
We will use Standard scaling.
We will drop the Week column as it is a row identifier and will not help in prediction of revenue¶
In [250]:
# removing columns
cameraaccessory_df = cameraaccessory_df.drop('Week', axis=1)
gamingaccessory_df = gamingaccessory_df.drop('Week', axis=1)
homeaudio_df = homeaudio_df.drop('Week', axis=1)
In [251]:
from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
cameraaccessory_df[cameraaccessory_df.columns]=scaler.fit_transform(cameraaccessory_df[cameraaccessory_df.columns])
gamingaccessory_df[gamingaccessory_df.columns]=scaler.fit_transform(gamingaccessory_df[gamingaccessory_df.columns])
homeaudio_df[homeaudio_df.columns]=scaler.fit_transform(homeaudio_df[homeaudio_df.columns])
cameraaccessory_df.head()
Out[251]:
Splitting the 3 Dataframes into Training and Testing Sets¶
As you know, the first basic step for regression is performing a train-test split.
In [252]:
from sklearn.model_selection import train_test_split
# We specify this so that the train and test data set always have the same rows, respectively
cameraaccessory_train, cameraaccessory_test = train_test_split(cameraaccessory_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
gamingaccessory_train, gamingaccessory_test = train_test_split(gamingaccessory_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
homeaudio_train, homeaudio_test = train_test_split(homeaudio_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
Dividing the 3 dataframes into X and Y sets for the model building¶
In [253]:
y_cameraaccessory_train = cameraaccessory_train.pop('gmv')
X_cameraaccessory_train = cameraaccessory_train
y_gamingaccessory_train = gamingaccessory_train.pop('gmv')
X_gamingaccessory_train = gamingaccessory_train
y_homeaudio_train = homeaudio_train.pop('gmv')
X_homeaudio_train = homeaudio_train
X_cameraaccessory_train.head()
Out[253]:
Dividing into X and Y test sets for the model building for 3 dataframes¶
In [254]:
y_cameraaccessory_test = cameraaccessory_test.pop('gmv')
X_cameraaccessory_test = cameraaccessory_test
y_gamingaccessory_test = gamingaccessory_test.pop('gmv')
X_gamingaccessory_test = gamingaccessory_test
y_homeaudio_test = homeaudio_test.pop('gmv')
X_homeaudio_test = homeaudio_test
X_cameraaccessory_test.head()
Out[254]:
Building Linear Regression model for cameraaccessory¶
In [255]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
cameraaccessory_model = LinearRegression().fit(X_cameraaccessory_train, y_cameraaccessory_train)
y_cameraaccessory_test_pred = cameraaccessory_model.predict(X_cameraaccessory_test)
print('R2 Score: {}'.format(r2_score(y_cameraaccessory_test, y_cameraaccessory_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_cameraaccessory_test, y_cameraaccessory_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.83 and mse of 0.17¶
Building Linear Regression model for cameraaccessory using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [256]:
y_cameraaccessory = cameraaccessory_df.pop('gmv')
X_cameraaccessory = cameraaccessory_df
In [257]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
cameraaccessory_model_cv = LinearRegression().fit(X_cameraaccessory, y_cameraaccessory)
cameraaccessory_predictions_cv = cross_val_predict(cameraaccessory_model_cv, X_cameraaccessory, y_cameraaccessory, cv=10)
accuracy = metrics.r2_score(y_cameraaccessory, cameraaccessory_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_cameraaccessory, cameraaccessory_predictions_cv)))
With Simple Linear Regression, using cross validation, we get a r2 score of -0.8 and mse score of 1.08¶
Here R2 score is negative which signifies that the chosen model does not follow the trend of the data, so it fits worse than a horizontal line. It simply means the chosen model (with its constraints) fits the data really poorly.
Determining Feature Importance for cameraaccessory from model without cv¶
In [258]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
cameraaccessory_lr_model_parameters = list(cameraaccessory_model.coef_)
cameraaccessory_lr_model_parameters.insert(0, cameraaccessory_model.intercept_)
cameraaccessory_lr_model_parameters = [round(x, 3) for x in cameraaccessory_lr_model_parameters]
cols = X_cameraaccessory_test.columns
cols = cols.insert(0, "constant")
cameraaccessory_lr_coef = list(zip(cols, cameraaccessory_lr_model_parameters))
cameraaccessory_lr_coef
Out[258]:
In [259]:
cameraaccessory_lr_coef_df = pd.DataFrame(cameraaccessory_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.rename(columns=col_rename)
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.iloc[1:,:]
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.loc[cameraaccessory_lr_coef_df['Coefficients']!=0.0]
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
cameraaccessory_lr_coef_df
Out[259]:
Plotting the Features in descending order of Importance for cameraaccessory¶
In [260]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 15), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=cameraaccessory_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for cameraaccessory are:¶
| Features | Coefficients |
|---|---|
| product_vertical_lens | 0.432 |
| product_vertical_camerabattery | 0.200 |
| product_vertical_camerabag | 0.182 |
| product_vertical_camerahousing | 0.171 |
| Online marketing | 0.151 |
Building Linear Regression model for gamingaccessory¶
In [261]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
gamingaccessory_model = LinearRegression().fit(X_gamingaccessory_train, y_gamingaccessory_train)
y_gamingaccessory_test_pred = gamingaccessory_model.predict(X_gamingaccessory_test)
print('R2 Score: {}'.format(r2_score(y_gamingaccessory_test, y_gamingaccessory_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_gamingaccessory_test, y_gamingaccessory_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.93 and mse of 0.05¶
Building Linear Regression model for gamingaccessory using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [262]:
y_gamingaccessory = gamingaccessory_df.pop('gmv')
X_gamingaccessory = gamingaccessory_df
In [263]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
gamingaccessory_model_cv = LinearRegression().fit(X_gamingaccessory, y_gamingaccessory)
gamingaccessory_predictions_cv = cross_val_predict(gamingaccessory_model_cv, X_gamingaccessory, y_gamingaccessory, cv=10)
accuracy = metrics.r2_score(y_gamingaccessory, gamingaccessory_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_gamingaccessory, gamingaccessory_predictions_cv)))
With Simple Linear Regression, using cross validation, we get r2 score of 0.51 and mse score of 0.49¶
Determining Feature Importance for gamingaccessory from model without cv¶
In [264]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
gamingaccessory_lr_model_parameters = list(gamingaccessory_model.coef_)
gamingaccessory_lr_model_parameters.insert(0, gamingaccessory_model.intercept_)
gamingaccessory_lr_model_parameters = [round(x, 3) for x in gamingaccessory_lr_model_parameters]
cols = X_gamingaccessory_test.columns
cols = cols.insert(0, "constant")
gamingaccessory_lr_coef = list(zip(cols, gamingaccessory_lr_model_parameters))
gamingaccessory_lr_coef
Out[264]:
In [265]:
gamingaccessory_lr_coef_df = pd.DataFrame(gamingaccessory_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.rename(columns=col_rename)
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.iloc[1:,:]
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.loc[gamingaccessory_lr_coef_df['Coefficients']!=0.0]
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
gamingaccessory_lr_coef_df
Out[265]:
Plotting the Features in descending order of Importance for gamingaccessory¶
In [266]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 15), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=gamingaccessory_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for gamingaccessory are:¶
| Features | Coefficients |
|---|---|
| product_vertical_gamepad | 0.201 |
| product_vertical_gamingheadset | 0.183 |
| is_mass_market | 0.167 |
| product_vertical_gamingaccessorykit | 0.126 |
| product_vertical_gamingmouse | 0.107 |
Building Linear Regression model for homeaudio¶
In [267]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
homeaudio_model = LinearRegression().fit(X_homeaudio_train, y_homeaudio_train)
y_homeaudio_test_pred = homeaudio_model.predict(X_homeaudio_test)
print('R2 Score: {}'.format(r2_score(y_homeaudio_test, y_homeaudio_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_homeaudio_test, y_homeaudio_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.96 and mse of 0.09¶
Building Linear Regression model for homeaudio using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [268]:
y_homeaudio = homeaudio_df.pop('gmv')
X_homeaudio = homeaudio_df
In [269]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
homeaudio_model_cv = LinearRegression().fit(X_homeaudio, y_homeaudio)
homeaudio_predictions_cv = cross_val_predict(homeaudio_model_cv, X_homeaudio, y_homeaudio, cv=5)
accuracy = metrics.r2_score(y_homeaudio, homeaudio_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_homeaudio, homeaudio_predictions_cv)))
With Simple Linear Regression, using cross validation, we get a r2 score of 0.73 and mse score of 0.27¶
Determining Feature Importance for homeaudio from model without cv¶
In [270]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
homeaudio_lr_model_parameters = list(homeaudio_model.coef_)
homeaudio_lr_model_parameters.insert(0, homeaudio_model.intercept_)
homeaudio_lr_model_parameters = [round(x, 3) for x in homeaudio_lr_model_parameters]
cols = homeaudio_test.columns
cols = cols.insert(0, "constant")
homeaudio_lr_coef = list(zip(cols, homeaudio_lr_model_parameters))
homeaudio_lr_coef
Out[270]:
In [271]:
homeaudio_lr_coef_df = pd.DataFrame(homeaudio_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
homeaudio_lr_coef_df = homeaudio_lr_coef_df.rename(columns=col_rename)
homeaudio_lr_coef_df = homeaudio_lr_coef_df.iloc[1:,:]
homeaudio_lr_coef_df = homeaudio_lr_coef_df.loc[homeaudio_lr_coef_df['Coefficients']!=0.0]
homeaudio_lr_coef_df = homeaudio_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
homeaudio_lr_coef_df
Out[271]:
Plotting the Features in descending order of Importance for homeaudio¶
In [272]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 15), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=homeaudio_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for homeaudio are:¶
| Features | Coefficients |
|---|---|
| product_vertical_homeaudiospeaker | 0.392 |
| is_mass_market | 0.148 |
| Digital_SMA_3 | 0.139 |
| product_vertical_fmradio | 0.133 |
| is_cod | 0.131 |
Multiplicative Model¶
The Linear Regression Model that we built earlier is an additive model that has been an implicit assumption that the different KPIs affect the revenue additively.
Y = α + β1X1 + β2X2 + β3X3 + β4X4 + β5X5 + ϵ
However when there are interactions between the KPIs, we go for a multiplicative model.
To fit a multiplicative model, take logarithms of the data(on both sides of the model), then analyse the log data as before.
Y = e^α .X1^β1 . X2^β2 . X3^β3 . X4^β4 . X5^β5 + ϵ
lnY = α + β1ln(X1) + β2ln(X2) + β3ln(X3) + β4ln(X4) + β5ln(X5) + ϵ'
In [273]:
homeaudio_org_df.head()
Out[273]:
In [274]:
# Making copy of dataframes from the original ones
cameraaccessory_mul_df = cameraaccessory_org_df.copy()
gamingaccessory_mul_df = gamingaccessory_org_df.copy()
homeaudio_mul_df = homeaudio_org_df.copy()
homeaudio_mul_df.head()
Out[274]:
In [275]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(homeaudio_mul_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(homeaudio_mul_df.isnull().sum()/homeaudio_mul_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[275]:
We will drop the Week column as it is a row identifier and will not help in prediction of revenue¶
In [276]:
# removing columns
cameraaccessory_mul_df = cameraaccessory_mul_df.drop('Week', axis=1)
gamingaccessory_mul_df = gamingaccessory_mul_df.drop('Week', axis=1)
homeaudio_mul_df = homeaudio_mul_df.drop('Week', axis=1)
homeaudio_mul_df.head()
Out[276]:
In [277]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(homeaudio_mul_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(homeaudio_mul_df.isnull().sum()/homeaudio_mul_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[277]:
In [278]:
cameraaccessory_mul_df = cameraaccessory_mul_df.applymap(lambda x: np.log(x))
cameraaccessory_mul_df = cameraaccessory_mul_df.replace([np.inf, -np.inf], 0)
cameraaccessory_mul_df = cameraaccessory_mul_df.replace(np.nan, 0)
gamingaccessory_mul_df = gamingaccessory_mul_df.applymap(lambda x: np.log(x))
gamingaccessory_mul_df = gamingaccessory_mul_df.replace([np.inf, -np.inf], 0)
gamingaccessory_mul_df = gamingaccessory_mul_df.replace(np.nan, 0)
homeaudio_mul_df = homeaudio_mul_df.applymap(lambda x: np.log(x))
homeaudio_mul_df = homeaudio_mul_df.replace([np.inf, -np.inf], 0)
homeaudio_mul_df = homeaudio_mul_df.replace(np.nan, 0)
homeaudio_mul_df.head()
Out[278]:
In [279]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(homeaudio_mul_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(homeaudio_mul_df.isnull().sum()/homeaudio_mul_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[279]:
Rescaling the Features of the 3 Dataframes¶
We will use Standard scaling.
In [280]:
from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
cameraaccessory_mul_df[cameraaccessory_mul_df.columns]=scaler.fit_transform(cameraaccessory_mul_df[cameraaccessory_mul_df.columns])
gamingaccessory_mul_df[gamingaccessory_mul_df.columns]=scaler.fit_transform(gamingaccessory_mul_df[gamingaccessory_mul_df.columns])
homeaudio_mul_df[homeaudio_mul_df.columns]=scaler.fit_transform(homeaudio_mul_df[homeaudio_mul_df.columns])
homeaudio_mul_df.head()
Out[280]:
Splitting the 3 Dataframes into Training and Testing Sets¶
As you know, the first basic step for regression is performing a train-test split.
In [281]:
from sklearn.model_selection import train_test_split
# We specify this so that the train and test data set always have the same rows, respectively
cameraaccessory_mul_train, cameraaccessory_mul_test = train_test_split(cameraaccessory_mul_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
gamingaccessory_mul_train, gamingaccessory_mul_test = train_test_split(gamingaccessory_mul_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
homeaudio_mul_train, homeaudio_mul_test = train_test_split(homeaudio_mul_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
Dividing the 3 dataframes into X and Y sets for the model building¶
In [282]:
y_cameraaccessory_mul_train = cameraaccessory_mul_train.pop('gmv')
X_cameraaccessory_mul_train = cameraaccessory_mul_train
y_gamingaccessory_mul_train = gamingaccessory_mul_train.pop('gmv')
X_gamingaccessory_mul_train = gamingaccessory_mul_train
y_homeaudio_mul_train = homeaudio_mul_train.pop('gmv')
X_homeaudio_mul_train = homeaudio_mul_train
X_homeaudio_mul_train.head()
Out[282]:
Dividing into X and Y test sets for the model building for 3 dataframes¶
In [283]:
y_cameraaccessory_mul_test = cameraaccessory_mul_test.pop('gmv')
X_cameraaccessory_mul_test = cameraaccessory_mul_test
y_gamingaccessory_mul_test = gamingaccessory_mul_test.pop('gmv')
X_gamingaccessory_mul_test = gamingaccessory_mul_test
y_homeaudio_mul_test = homeaudio_mul_test.pop('gmv')
X_homeaudio_mul_test = homeaudio_mul_test
X_homeaudio_mul_test.head()
Out[283]:
Building Linear Regression model for cameraaccessory¶
In [284]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
cameraaccessory_mul_model = LinearRegression().fit(X_cameraaccessory_mul_train, y_cameraaccessory_mul_train)
y_cameraaccessory_mul_test_pred = cameraaccessory_mul_model.predict(X_cameraaccessory_mul_test)
print('R2 Score: {}'.format(r2_score(y_cameraaccessory_mul_test, y_cameraaccessory_mul_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_cameraaccessory_mul_test, y_cameraaccessory_mul_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.84 and mse of 0.36¶
Building Linear Regression model for cameraaccessory using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [285]:
y_cameraaccessory_mul = cameraaccessory_mul_df.pop('gmv')
X_cameraaccessory_mul = cameraaccessory_mul_df
In [286]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
cameraaccessory_mul_model_cv = LinearRegression().fit(X_cameraaccessory_mul, y_cameraaccessory_mul)
cameraaccessory_mul_predictions_cv = cross_val_predict(cameraaccessory_mul_model_cv, X_cameraaccessory_mul, \
y_cameraaccessory_mul, cv=10)
accuracy = metrics.r2_score(y_cameraaccessory_mul, cameraaccessory_mul_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_cameraaccessory_mul, cameraaccessory_mul_predictions_cv)))
With Simple Linear Regression, using cross validation, we get a r2 score of 0.91 and mse score of 0.09¶
Determining Feature Importance for cameraaccessory from model with cv¶
In [287]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
cameraaccessory_mul_lr_model_parameters = list(cameraaccessory_mul_model_cv.coef_)
cameraaccessory_mul_lr_model_parameters.insert(0, cameraaccessory_mul_model_cv.intercept_)
cameraaccessory_mul_lr_model_parameters = [round(x, 3) for x in cameraaccessory_mul_lr_model_parameters]
cols = X_cameraaccessory_test.columns
cols = cols.insert(0, "constant")
cameraaccessory_mul_lr_coef = list(zip(cols, cameraaccessory_mul_lr_model_parameters))
cameraaccessory_mul_lr_coef
Out[287]:
In [288]:
cameraaccessory_mul_lr_coef_df = pd.DataFrame(cameraaccessory_mul_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
cameraaccessory_mul_lr_coef_df = cameraaccessory_mul_lr_coef_df.rename(columns=col_rename)
cameraaccessory_mul_lr_coef_df = cameraaccessory_mul_lr_coef_df.iloc[1:,:]
cameraaccessory_mul_lr_coef_df = cameraaccessory_mul_lr_coef_df.loc[cameraaccessory_mul_lr_coef_df['Coefficients']!=0.0]
cameraaccessory_mul_lr_coef_df = cameraaccessory_mul_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
cameraaccessory_mul_lr_coef_df
Out[288]:
Plotting the Features in descending order of Importance for cameraaccessory¶
In [289]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 15), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=cameraaccessory_mul_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for cameraaccessory are:¶
| Features | Coefficients |
|---|---|
| product_vertical_lens | 0.181 |
| product_vertical_camerabattery | 0.160 |
| is_mass_market | 0.149 |
| product_vertical_camerabatterycharger | 0.121 |
| TV | 0.105 |
Building Linear Regression model for gamingaccessory¶
In [290]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
gamingaccessory_mul_model = LinearRegression().fit(X_gamingaccessory_mul_train, y_gamingaccessory_mul_train)
y_gamingaccessory_mul_test_pred = gamingaccessory_mul_model.predict(X_gamingaccessory_mul_test)
print('R2 Score: {}'.format(r2_score(y_gamingaccessory_mul_test, y_gamingaccessory_mul_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_gamingaccessory_mul_test, y_gamingaccessory_mul_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.94 and mse of 0.09¶
Building Linear Regression model for gamingaccessory using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [291]:
y_gamingaccessory_mul = gamingaccessory_mul_df.pop('gmv')
X_gamingaccessory_mul = gamingaccessory_mul_df
In [292]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
gamingaccessory_mul_model_cv = LinearRegression().fit(X_gamingaccessory_mul, y_gamingaccessory_mul)
gamingaccessory_mul_predictions_cv = cross_val_predict(gamingaccessory_mul_model_cv, X_gamingaccessory_mul, \
y_gamingaccessory_mul, cv=10)
accuracy = metrics.r2_score(y_gamingaccessory_mul, gamingaccessory_mul_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_gamingaccessory_mul, gamingaccessory_mul_predictions_cv)))
With Simple Linear Regression, using cross validation, we get a r2 score of 0.94 and mse score of 0.06¶
Determining Feature Importance for gamingaccessory with model with cv¶
In [293]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
gamingaccessory_mul_lr_model_parameters = list(gamingaccessory_mul_model_cv.coef_)
gamingaccessory_mul_lr_model_parameters.insert(0, gamingaccessory_mul_model_cv.intercept_)
gamingaccessory_mul_lr_model_parameters = [round(x, 3) for x in gamingaccessory_mul_lr_model_parameters]
cols = X_gamingaccessory_test.columns
cols = cols.insert(0, "constant")
gamingaccessory_mul_lr_coef = list(zip(cols, gamingaccessory_mul_lr_model_parameters))
gamingaccessory_mul_lr_coef
Out[293]:
In [294]:
gamingaccessory_mul_lr_coef_df = pd.DataFrame(gamingaccessory_mul_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
gamingaccessory_mul_lr_coef_df = gamingaccessory_mul_lr_coef_df.rename(columns=col_rename)
gamingaccessory_mul_lr_coef_df = gamingaccessory_mul_lr_coef_df.iloc[1:,:]
gamingaccessory_mul_lr_coef_df = gamingaccessory_mul_lr_coef_df.loc[gamingaccessory_mul_lr_coef_df['Coefficients']!=0.0]
gamingaccessory_mul_lr_coef_df = gamingaccessory_mul_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
gamingaccessory_mul_lr_coef_df
Out[294]:
Plotting the Features in descending order of Importance for gamingaccessory¶
In [295]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 15), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=gamingaccessory_mul_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for gamingaccessory are:¶
| Features | Coefficients |
|---|---|
| product_vertical_gamingheadset | 0.250 |
| is_mass_market | 0.234 |
| product_vertical_gamingmouse | 0.224 |
| product_vertical_gamepad | 0.211 |
| Online marketing_SMA_3 | 0.157 |
Building Linear Regression model for homeaudio¶
In [296]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
homeaudio_mul_model = LinearRegression().fit(X_homeaudio_mul_train, y_homeaudio_mul_train)
y_homeaudio_mul_test_pred = homeaudio_mul_model.predict(X_homeaudio_mul_test)
print('R2 Score: {}'.format(r2_score(y_homeaudio_mul_test, y_homeaudio_mul_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_homeaudio_mul_test, y_homeaudio_mul_test_pred)))
With Simple Linear Regression, we get a r2 score of -0.63 and mse of 0.34¶
Here R2 is negative which signifies that the chosen model does not follow the trend of the data, so fits worse than a horizontal line. It simply means the chosen model (with its constraints) fits the data really poorly.
Building Linear Regression model for homeaudio using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [297]:
y_homeaudio_mul = homeaudio_mul_df.pop('gmv')
X_homeaudio_mul = homeaudio_mul_df
In [298]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
homeaudio_mul_model_cv = LinearRegression().fit(X_homeaudio_mul, y_homeaudio_mul)
homeaudio_mul_predictions_cv = cross_val_predict(homeaudio_mul_model_cv, X_homeaudio_mul, y_homeaudio_mul, cv=5)
accuracy = metrics.r2_score(y_homeaudio_mul, homeaudio_mul_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_homeaudio_mul, homeaudio_mul_predictions_cv)))
With Simple Linear Regression, using cross validation, we get a r2 score of 0.86 and mse score of 0.14¶
Determining Feature Importance for homeaudio with model with cv¶
In [299]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
homeaudio_mul_lr_model_parameters = list(homeaudio_mul_model_cv.coef_)
homeaudio_mul_lr_model_parameters.insert(0, homeaudio_mul_model_cv.intercept_)
homeaudio_mul_lr_model_parameters = [round(x, 3) for x in homeaudio_mul_lr_model_parameters]
cols = X_homeaudio_test.columns
cols = cols.insert(0, "constant")
homeaudio_mul_lr_coef = list(zip(cols, homeaudio_mul_lr_model_parameters))
homeaudio_mul_lr_coef
Out[299]:
In [300]:
homeaudio_mul_lr_coef_df = pd.DataFrame(homeaudio_mul_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
homeaudio_mul_lr_coef_df = homeaudio_mul_lr_coef_df.rename(columns=col_rename)
homeaudio_mul_lr_coef_df = homeaudio_mul_lr_coef_df.iloc[1:,:]
homeaudio_mul_lr_coef_df = homeaudio_mul_lr_coef_df.loc[homeaudio_mul_lr_coef_df['Coefficients']!=0.0]
homeaudio_mul_lr_coef_df = homeaudio_mul_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
homeaudio_mul_lr_coef_df
Out[300]:
Plotting the Features in descending order of Importance for homeaudio¶
In [301]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 15), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=homeaudio_mul_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for homeaudio are:¶
| Features | Coefficients |
|---|---|
| product_vertical_homeaudiospeaker | 0.469 |
| is_mass_market | 0.289 |
| product_vertical_fmradio | 0.224 |
| Radio_Ad_Stock | 0.147 |
| Sponsorship | 0.121 |
Koyck Model¶
The Additive and Multiplicative Linear Models that we have built till now can capture the current effect of the KPIs. However, if we want to capture the carry-over effect, we would want to model the current revenue figures based on the past figures of the KPIs.
The Koyck tells us that the current revenue generated is not just influenced by the different independent attributes, but also because of the revenue generated over the last periods. ie. Current revenue(Yt) is also dependent on the past revenue values(Yt-1).
Yt = α + β1X1 + β2X2 + β3X3 + β4X4 + β5X5 + ϵ
Yt = α + µYt-1 + β1X1 + β2X2 + β3X3 + β4X4 + β5X5 + ϵ -- (sale at time t is dependent on sale at time t-1)
If X1 is the advertising effect, β1 is the current effect of advertising, carry over effect of advertising is β1 µ/(1-µ).*
Therefore the total effect of advertising = Current effect + Carry over effect
= β1 + β1 * µ/(1-µ)
= β1/(1-µ)In [302]:
homeaudio_org_df.head()
Out[302]:
In [303]:
# Making copy of dataframes from the original ones
cameraaccessory_koy_df = cameraaccessory_org_df.copy()
gamingaccessory_koy_df = gamingaccessory_org_df.copy()
homeaudio_koy_df = homeaudio_org_df.copy()
homeaudio_koy_df.head()
Out[303]:
In [304]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(homeaudio_koy_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(homeaudio_koy_df.isnull().sum()/homeaudio_koy_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[304]:
Creating a new variable which is the Lag of 1 week of the dependent variable (GMV)¶
In [305]:
GMV_Lag = ['gmv']
In [306]:
def lag_variables(df,var,n):
for i in var:
loc_index = df.columns.get_loc(i) + 1
df.insert(loc=loc_index,column= i+'_lag'+np.str(n),value=df[i].shift(n))
return df
In [307]:
cameraaccessory_koy_df = lag_variables(cameraaccessory_koy_df,GMV_Lag,1)
gamingaccessory_koy_df = lag_variables(gamingaccessory_koy_df,GMV_Lag,1)
homeaudio_koy_df = lag_variables(homeaudio_koy_df,GMV_Lag,1)
homeaudio_koy_df.head()
Out[307]:
In [308]:
# Imputing all null values with 0
cameraaccessory_koy_df.fillna(value=0, inplace=True)
gamingaccessory_koy_df.fillna(value=0, inplace=True)
homeaudio_koy_df.fillna(value=0, inplace=True)
homeaudio_koy_df.head(10)
Out[308]:
We will drop the Week column as it is a row identifier and will not help in prediction of revenue¶
In [309]:
# removing columns
cameraaccessory_koy_df = cameraaccessory_koy_df.drop('Week', axis=1)
gamingaccessory_koy_df = gamingaccessory_koy_df.drop('Week', axis=1)
homeaudio_koy_df = homeaudio_koy_df.drop('Week', axis=1)
homeaudio_koy_df.head()
Out[309]:
In [310]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(homeaudio_koy_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(homeaudio_koy_df.isnull().sum()/homeaudio_koy_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[310]:
Rescaling the Features of the 3 Dataframes¶
We will use Standard scaling.
In [311]:
from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
cameraaccessory_koy_df[cameraaccessory_koy_df.columns]=scaler.fit_transform(cameraaccessory_koy_df[cameraaccessory_koy_df.columns])
gamingaccessory_koy_df[gamingaccessory_koy_df.columns]=scaler.fit_transform(gamingaccessory_koy_df[gamingaccessory_koy_df.columns])
homeaudio_koy_df[homeaudio_koy_df.columns]=scaler.fit_transform(homeaudio_koy_df[homeaudio_koy_df.columns])
homeaudio_koy_df.head()
Out[311]:
Splitting the 3 Dataframes into Training and Testing Sets¶
As you know, the first basic step for regression is performing a train-test split.
In [312]:
from sklearn.model_selection import train_test_split
# We specify this so that the train and test data set always have the same rows, respectively
cameraaccessory_train, cameraaccessory_test = train_test_split(cameraaccessory_koy_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
gamingaccessory_train, gamingaccessory_test = train_test_split(gamingaccessory_koy_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
homeaudio_train, homeaudio_test = train_test_split(homeaudio_koy_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
Dividing the 3 dataframes into X and Y sets for the model building¶
In [313]:
y_cameraaccessory_train = cameraaccessory_train.pop('gmv')
X_cameraaccessory_train = cameraaccessory_train
y_gamingaccessory_train = gamingaccessory_train.pop('gmv')
X_gamingaccessory_train = gamingaccessory_train
y_homeaudio_train = homeaudio_train.pop('gmv')
X_homeaudio_train = homeaudio_train
X_homeaudio_train.head()
Out[313]:
Dividing into X and Y test sets for the model building for 3 dataframes¶
In [314]:
y_cameraaccessory_test = cameraaccessory_test.pop('gmv')
X_cameraaccessory_test = cameraaccessory_test
y_gamingaccessory_test = gamingaccessory_test.pop('gmv')
X_gamingaccessory_test = gamingaccessory_test
y_homeaudio_test = homeaudio_test.pop('gmv')
X_homeaudio_test = homeaudio_test
X_homeaudio_test.head()
Out[314]:
Building Linear Regression model for cameraaccessory¶
In [315]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
cameraaccessory_model = LinearRegression().fit(X_cameraaccessory_train, y_cameraaccessory_train)
y_cameraaccessory_test_pred = cameraaccessory_model.predict(X_cameraaccessory_test)
print('R2 Score: {}'.format(r2_score(y_cameraaccessory_test, y_cameraaccessory_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_cameraaccessory_test, y_cameraaccessory_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.84 and mse of 0.16¶
Building Linear Regression model for cameraaccessory using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [316]:
y_cameraaccessory = cameraaccessory_koy_df.pop('gmv')
X_cameraaccessory = cameraaccessory_koy_df
In [317]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
cameraaccessory_model_cv = LinearRegression().fit(X_cameraaccessory, y_cameraaccessory)
cameraaccessory_predictions_cv = cross_val_predict(cameraaccessory_model_cv, X_cameraaccessory, y_cameraaccessory, cv=10)
accuracy = metrics.r2_score(y_cameraaccessory, cameraaccessory_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_cameraaccessory, cameraaccessory_predictions_cv)))
With Simple Linear Regression, using cross validation, we get a r2 score of 0.27 and mse score of 0.73¶
Determining Feature Importance for cameraaccessory with model without cv¶
In [318]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
cameraaccessory_lr_model_parameters = list(cameraaccessory_model.coef_)
cameraaccessory_lr_model_parameters.insert(0, cameraaccessory_model.intercept_)
cameraaccessory_lr_model_parameters = [round(x, 3) for x in cameraaccessory_lr_model_parameters]
cols = X_cameraaccessory_test.columns
cols = cols.insert(0, "constant")
cameraaccessory_lr_coef = list(zip(cols, cameraaccessory_lr_model_parameters))
cameraaccessory_lr_coef
Out[318]:
In [319]:
cameraaccessory_lr_coef_df = pd.DataFrame(cameraaccessory_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.rename(columns=col_rename)
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.iloc[1:,:]
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.loc[cameraaccessory_lr_coef_df['Coefficients']!=0.0]
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
cameraaccessory_lr_coef_df
Out[319]:
Determining Total Effect of KPIs from Koyck model¶
Equation for Koyck Model:
Yt = α + µYt-1 + β1X1 + β2X2 + β3X3 + β4X4 + β5X5 + ϵ -- (sale at time t is dependent on sale at time t-1)
If X1 is the advertising effect, β1 is the current effect of advertising, carry over effect of advertising is β1 µ/(1-µ).*
Therefore the total effect of advertising = Current effect + Carry over effect
= β1 + β1 * µ/(1-µ)
= β1/(1-µ)Calculating the coefficient of lag variable¶
In [320]:
cameraaccessory_lr_coef_df.loc[cameraaccessory_lr_coef_df['Features'] == 'gmv_lag1'].Coefficients
Out[320]:
In [321]:
cameraaccessory_lr_coef_df['Total Effect'] = cameraaccessory_lr_coef_df['Coefficients']/(1-(-0.128))
cameraaccessory_lr_coef_df
Out[321]:
Plotting the Features in descending order of Importance for cameraaccessory¶
In [322]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 15), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Total Effect', palette='husl', data=cameraaccessory_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for cameraaccessory are:¶
| Features | Coefficients |
|---|---|
| product_vertical_lens | 0.299 |
| product_vertical_camerabag | 0.195 |
| product_vertical_camerahousing | 0.135 |
| product_vertical_camerabattery | 0.133 |
| Online marketing | 0.127 |
Building Linear Regression model for gamingaccessory¶
In [323]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
gamingaccessory_model = LinearRegression().fit(X_gamingaccessory_train, y_gamingaccessory_train)
y_gamingaccessory_test_pred = gamingaccessory_model.predict(X_gamingaccessory_test)
print('R2 Score: {}'.format(r2_score(y_gamingaccessory_test, y_gamingaccessory_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_gamingaccessory_test, y_gamingaccessory_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.93 and mse of 0.05¶
Building Linear Regression model for gamingaccessory using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [324]:
y_gamingaccessory = gamingaccessory_koy_df.pop('gmv')
X_gamingaccessory = gamingaccessory_koy_df
In [325]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
gamingaccessory_model_cv = LinearRegression().fit(X_gamingaccessory, y_gamingaccessory)
gamingaccessory_predictions_cv = cross_val_predict(gamingaccessory_model_cv, X_gamingaccessory, y_gamingaccessory, cv=10)
accuracy = metrics.r2_score(y_gamingaccessory, gamingaccessory_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_gamingaccessory, gamingaccessory_predictions_cv)))
With Simple Linear Regression, using cross validation, we get a r2 score of 0.49 and mse score of 0.51¶
Determining Feature Importance for gamingaccessory with model without cv¶
In [326]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
gamingaccessory_lr_model_parameters = list(gamingaccessory_model.coef_)
gamingaccessory_lr_model_parameters.insert(0, gamingaccessory_model.intercept_)
gamingaccessory_lr_model_parameters = [round(x, 3) for x in gamingaccessory_lr_model_parameters]
cols = X_gamingaccessory_test.columns
cols = cols.insert(0, "constant")
gamingaccessory_lr_coef = list(zip(cols, gamingaccessory_lr_model_parameters))
gamingaccessory_lr_coef
Out[326]:
In [327]:
gamingaccessory_lr_coef_df = pd.DataFrame(gamingaccessory_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.rename(columns=col_rename)
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.iloc[1:,:]
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.loc[gamingaccessory_lr_coef_df['Coefficients']!=0.0]
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
gamingaccessory_lr_coef_df
Out[327]:
Determining Total Effect of KPIs from Koyck model¶
Equation for Koyck Model:
Yt = α + µYt-1 + β1X1 + β2X2 + β3X3 + β4X4 + β5X5 + ϵ -- (sale at time t is dependent on sale at time t-1)
If X1 is the advertising effect, β1 is the current effect of advertising, carry over effect of advertising is β1 µ/(1-µ).*
Therefore the total effect of advertising = Current effect + Carry over effect
= β1 + β1 * µ/(1-µ)
= β1/(1-µ)Calculating the coefficient of lag variable¶
In [328]:
gamingaccessory_lr_coef_df.loc[gamingaccessory_lr_coef_df['Features'] == 'gmv_lag1'].Coefficients
Out[328]:
In [329]:
gamingaccessory_lr_coef_df['Total Effect'] = gamingaccessory_lr_coef_df['Coefficients']/(1-(-0.015))
gamingaccessory_lr_coef_df
Out[329]:
Plotting the Features in descending order of Importance for gamingaccessory¶
In [330]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 15), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Total Effect', palette='husl', data=gamingaccessory_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for gamingaccessory are:¶
| Features | Coefficients |
|---|---|
| product_vertical_gamingheadset | 0.199 |
| product_vertical_gamingheadset | 0.172 |
| is_mass_market | 0.163 |
| product_vertical_gamingmouse | 0.125 |
| product_vertical_gamingaccessorykit | 0.106 |
Building Linear Regression model for homeaudio¶
In [331]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
homeaudio_model = LinearRegression().fit(X_homeaudio_train, y_homeaudio_train)
y_homeaudio_test_pred = homeaudio_model.predict(X_homeaudio_test)
print('R2 Score: {}'.format(r2_score(y_homeaudio_test, y_homeaudio_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_homeaudio_test, y_homeaudio_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.96 and mse of 0.09¶
Building Linear Regression model for homeaudio using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [332]:
y_homeaudio = homeaudio_koy_df.pop('gmv')
X_homeaudio = homeaudio_koy_df
In [333]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
homeaudio_model_cv = LinearRegression().fit(X_homeaudio, y_homeaudio)
homeaudio_predictions_cv = cross_val_predict(homeaudio_model_cv, X_homeaudio, y_homeaudio, cv=5)
accuracy = metrics.r2_score(y_homeaudio, homeaudio_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_homeaudio, homeaudio_predictions_cv)))
With Simple Linear Regression, using cross validation, we get a r2 score of 0.70 and mse score of 0.30¶
Determining Feature Importance for homeaudio with model without cv¶
In [334]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
homeaudio_lr_model_parameters = list(homeaudio_model.coef_)
homeaudio_lr_model_parameters.insert(0, homeaudio_model.intercept_)
homeaudio_lr_model_parameters = [round(x, 3) for x in homeaudio_lr_model_parameters]
cols = homeaudio_test.columns
cols = cols.insert(0, "constant")
homeaudio_lr_coef = list(zip(cols, homeaudio_lr_model_parameters))
homeaudio_lr_coef
Out[334]:
In [335]:
homeaudio_lr_coef_df = pd.DataFrame(homeaudio_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
homeaudio_lr_coef_df = homeaudio_lr_coef_df.rename(columns=col_rename)
homeaudio_lr_coef_df = homeaudio_lr_coef_df.iloc[1:,:]
homeaudio_lr_coef_df = homeaudio_lr_coef_df.loc[homeaudio_lr_coef_df['Coefficients']!=0.0]
homeaudio_lr_coef_df = homeaudio_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
homeaudio_lr_coef_df
Out[335]:
Determining Total Effect of KPIs from Koyck model¶
Equation for Koyck Model:
Yt = α + µYt-1 + β1X1 + β2X2 + β3X3 + β4X4 + β5X5 + ϵ -- (sale at time t is dependent on sale at time t-1)
If X1 is the advertising effect, β1 is the current effect of advertising, carry over effect of advertising is β1 µ/(1-µ).*
Therefore the total effect of advertising = Current effect + Carry over effect
= β1 + β1 * µ/(1-µ)
= β1/(1-µ)Calculating the coefficient of lag variable¶
In [336]:
homeaudio_lr_coef_df.loc[homeaudio_lr_coef_df['Features'] == 'gmv_lag1'].Coefficients
Out[336]:
In [337]:
homeaudio_lr_coef_df['Total Effect'] = homeaudio_lr_coef_df['Coefficients']/(1-(0.060))
homeaudio_lr_coef_df
Out[337]:
Plotting the Features in descending order of Importance for homeaudio¶
In [338]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 15), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Total Effect', palette='husl', data=homeaudio_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for homeaudio are:¶
| Features | Coefficients |
|---|---|
| product_vertical_homeaudiospeaker | 0.416 |
| is_mass_market | 0.210 |
| is_cod | 0.162 |
| NPS | 0.120 |
| Mean Temp | 0.107 |
In [ ]:
In [ ]:
Distributive Lag Model(Additive)¶
The Additive and Multiplicative Linear Models that we have built till now can capture the current effect of the KPIs. However, if we want to capture the carry-over effect, we would want to model the current revenue figures based on the past figures of the KPIs.
In the distributed lag model, not only is the dependent variable entered in its lagged version, but the independent variables are as well. This is a more generalist model and captures the carry-over effect of all the variables. Thus, the Koyck model is a special type of distributed lag model, which includes the lag value of only the dependent variable.
Yt = α+ µ1Yt-1 + µ2Yt-2 + µ3Yt-3 + ....
+ β1X1t + β1X1t-1 + β1X1t-2 + ....
+ β2X2t + β2X2t-1 + β2X2t-2 + ....
+ β3X3t + β3X3t-1 + β3X3t-2 + ....
+ β4X4t + β4X4t-1 + β4X4t-2 + ....
+ β5X5t + β5X5t-1 + β5X5t-2 + ....
+ ϵIn [339]:
homeaudio_org_df.head()
Out[339]:
In [340]:
# Making copy of dataframes from the original ones
cameraaccessory_dladd_df = cameraaccessory_org_df.copy()
gamingaccessory_dladd_df = gamingaccessory_org_df.copy()
homeaudio_dladd_df = homeaudio_org_df.copy()
homeaudio_dladd_df.head()
Out[340]:
In [341]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(homeaudio_dladd_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(homeaudio_dladd_df.isnull().sum()/homeaudio_dladd_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[341]:
We will drop the Week column as it is a row identifier and will not help in prediction of revenue¶
In [342]:
# removing columns
cameraaccessory_dladd_df = cameraaccessory_dladd_df.drop('Week', axis=1)
gamingaccessory_dladd_df = gamingaccessory_dladd_df.drop('Week', axis=1)
homeaudio_dladd_df = homeaudio_dladd_df.drop('Week', axis=1)
homeaudio_dladd_df.head()
Out[342]:
Creating new lag(Lag of 3 weeks) variables for the dependent variable (GMV) as well as independent variables¶
In [343]:
cameraaccessory_dladd_df_columns = cameraaccessory_dladd_df.columns
gamingaccessory_dladd_df_columns = gamingaccessory_dladd_df.columns
homeaudio_dladd_df_columns = homeaudio_dladd_df.columns
In [344]:
cameraaccessory_dladd_df = lag_variables(cameraaccessory_dladd_df,cameraaccessory_dladd_df_columns,3)
gamingaccessory_dladd_df = lag_variables(gamingaccessory_dladd_df,gamingaccessory_dladd_df_columns,3)
homeaudio_dladd_df = lag_variables(homeaudio_dladd_df,homeaudio_dladd_df_columns,3)
homeaudio_dladd_df.head()
Out[344]:
Creating new lag(Lag of 2 weeks) variables for the dependent variable (GMV) as well as independent variables¶
In [345]:
cameraaccessory_dladd_df = lag_variables(cameraaccessory_dladd_df,cameraaccessory_dladd_df_columns,2)
gamingaccessory_dladd_df = lag_variables(gamingaccessory_dladd_df,gamingaccessory_dladd_df_columns,2)
homeaudio_dladd_df = lag_variables(homeaudio_dladd_df,homeaudio_dladd_df_columns,2)
homeaudio_dladd_df.head()
Out[345]:
Creating new lag(Lag of 1 week) variables for the dependent variable (GMV) as well as independent variables¶
In [346]:
cameraaccessory_dladd_df = lag_variables(cameraaccessory_dladd_df,cameraaccessory_dladd_df_columns,1)
gamingaccessory_dladd_df = lag_variables(gamingaccessory_dladd_df,gamingaccessory_dladd_df_columns,1)
homeaudio_dladd_df = lag_variables(homeaudio_dladd_df,homeaudio_dladd_df_columns,1)
homeaudio_dladd_df.head()
Out[346]:
Imputing all null values with 0¶
In [347]:
# Imputing all null values with 0
cameraaccessory_dladd_df.fillna(value=0, inplace=True)
gamingaccessory_dladd_df.fillna(value=0, inplace=True)
homeaudio_dladd_df.fillna(value=0, inplace=True)
homeaudio_dladd_df.head(10)
Out[347]:
In [348]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(homeaudio_dladd_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(homeaudio_dladd_df.isnull().sum()/homeaudio_dladd_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[348]:
Rescaling the Features of the 3 Dataframes¶
We will use Standard scaling.
In [349]:
from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
cameraaccessory_dladd_df[cameraaccessory_dladd_df.columns]=scaler.fit_transform(cameraaccessory_dladd_df[cameraaccessory_dladd_df.columns])
gamingaccessory_dladd_df[gamingaccessory_dladd_df.columns]=scaler.fit_transform(gamingaccessory_dladd_df[gamingaccessory_dladd_df.columns])
homeaudio_dladd_df[homeaudio_dladd_df.columns]=scaler.fit_transform(homeaudio_dladd_df[homeaudio_dladd_df.columns])
homeaudio_dladd_df.head()
Out[349]:
Splitting the 3 Dataframes into Training and Testing Sets¶
As you know, the first basic step for regression is performing a train-test split.
In [350]:
from sklearn.model_selection import train_test_split
# We specify this so that the train and test data set always have the same rows, respectively
cameraaccessory_dladd_train, cameraaccessory_dladd_test = train_test_split(cameraaccessory_dladd_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
gamingaccessory_dladd_train, gamingaccessory_dladd_test = train_test_split(gamingaccessory_dladd_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
homeaudio_dladd_train, homeaudio_dladd_test = train_test_split(homeaudio_dladd_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
Dividing the 3 dataframes into X and Y sets for the model building¶
In [351]:
y_cameraaccessory_dladd_train = cameraaccessory_dladd_train.pop('gmv')
X_cameraaccessory_dladd_train = cameraaccessory_dladd_train
y_gamingaccessory_dladd_train = gamingaccessory_dladd_train.pop('gmv')
X_gamingaccessory_dladd_train = gamingaccessory_dladd_train
y_homeaudio_dladd_train = homeaudio_dladd_train.pop('gmv')
X_homeaudio_dladd_train = homeaudio_dladd_train
X_homeaudio_dladd_train.head()
Out[351]:
Dividing into X and Y test sets for the model building for 3 dataframes¶
In [352]:
y_cameraaccessory_dladd_test = cameraaccessory_dladd_test.pop('gmv')
X_cameraaccessory_dladd_test = cameraaccessory_dladd_test
y_gamingaccessory_dladd_test = gamingaccessory_dladd_test.pop('gmv')
X_gamingaccessory_dladd_test = gamingaccessory_dladd_test
y_homeaudio_dladd_test = homeaudio_dladd_test.pop('gmv')
X_homeaudio_dladd_test = homeaudio_dladd_test
X_homeaudio_dladd_test.head()
Out[352]:
Building Linear Regression model for cameraaccessory¶
In [353]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
cameraaccessory_dladd_model = LinearRegression().fit(X_cameraaccessory_dladd_train, y_cameraaccessory_dladd_train)
y_cameraaccessory_dladd_test_pred = cameraaccessory_dladd_model.predict(X_cameraaccessory_dladd_test)
print('R2 Score: {}'.format(r2_score(y_cameraaccessory_dladd_test, y_cameraaccessory_dladd_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_cameraaccessory_dladd_test, y_cameraaccessory_dladd_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.87 and mse of 0.12¶
Building Linear Regression model for cameraaccessory using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [354]:
y_cameraaccessory_dladd = cameraaccessory_dladd_df.pop('gmv')
X_cameraaccessory_dladd = cameraaccessory_dladd_df
In [355]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
cameraaccessory_dladd_model_cv = LinearRegression().fit(X_cameraaccessory_dladd, y_cameraaccessory_dladd)
cameraaccessory_dladd_predictions_cv = cross_val_predict(cameraaccessory_dladd_model_cv, X_cameraaccessory_dladd, \
y_cameraaccessory_dladd, cv=10)
accuracy = metrics.r2_score(y_cameraaccessory_dladd, cameraaccessory_dladd_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_cameraaccessory_dladd, cameraaccessory_dladd_predictions_cv)))
With Simple Linear Regression, using cross validation, we get r2 score of 0.82 and mse score of 0.17¶
Determining Feature Importance for cameraaccessory with model with cv¶
In [356]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
cameraaccessory_lr_model_parameters = list(cameraaccessory_dladd_model_cv.coef_)
cameraaccessory_lr_model_parameters.insert(0, cameraaccessory_dladd_model_cv.intercept_)
cameraaccessory_lr_model_parameters = [round(x, 3) for x in cameraaccessory_lr_model_parameters]
cols = X_cameraaccessory_dladd_test.columns
cols = cols.insert(0, "constant")
cameraaccessory_lr_coef = list(zip(cols, cameraaccessory_lr_model_parameters))
cameraaccessory_lr_coef
Out[356]:
In [357]:
cameraaccessory_lr_coef_df = pd.DataFrame(cameraaccessory_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.rename(columns=col_rename)
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.iloc[1:,:]
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.loc[cameraaccessory_lr_coef_df['Coefficients']!=0.0]
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
cameraaccessory_lr_coef_df
Out[357]:
Plotting the Features in descending order of Importance for cameraaccessory¶
In [358]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 35), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=cameraaccessory_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for cameraaccessory are:¶
| Features | Coefficients |
|---|---|
| product_vertical_lens | 0.121 |
| product_vertical_filter | 0.117 |
| product_vertical_camerabag | 0.102 |
| product_vertical_cameraremotecontrol | 0.094 |
| is_mass_market | 0.079 |
Building Linear Regression model for gamingaccessory¶
In [359]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
gamingaccessory_dladd_model = LinearRegression().fit(X_gamingaccessory_dladd_train, y_gamingaccessory_dladd_train)
y_gamingaccessory_dladd_test_pred = gamingaccessory_dladd_model.predict(X_gamingaccessory_dladd_test)
print('R2 Score: {}'.format(r2_score(y_gamingaccessory_dladd_test, y_gamingaccessory_dladd_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_gamingaccessory_dladd_test, y_gamingaccessory_dladd_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.87 and mse of 0.10¶
Building Linear Regression model for gamingaccessory using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [360]:
y_gamingaccessory_dladd = gamingaccessory_dladd_df.pop('gmv')
X_gamingaccessory_dladd = gamingaccessory_dladd_df
In [361]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
gamingaccessory_dladd_model_cv = LinearRegression().fit(X_gamingaccessory_dladd, y_gamingaccessory_dladd)
gamingaccessory_dladd_predictions_cv = cross_val_predict(gamingaccessory_dladd_model_cv, X_gamingaccessory_dladd, \
y_gamingaccessory_dladd, cv=10)
accuracy = metrics.r2_score(y_gamingaccessory_dladd, gamingaccessory_dladd_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_gamingaccessory_dladd, gamingaccessory_dladd_predictions_cv)))
With Simple Linear Regression, using cross validation, we get a r2 score of 0.92 and mse score of 0.08¶
Determining Feature Importance for gamingaccessory with model with cv¶
In [362]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
gamingaccessory_lr_model_parameters = list(gamingaccessory_dladd_model_cv.coef_)
gamingaccessory_lr_model_parameters.insert(0, gamingaccessory_dladd_model_cv.intercept_)
gamingaccessory_lr_model_parameters = [round(x, 3) for x in gamingaccessory_lr_model_parameters]
cols = X_gamingaccessory_dladd_test.columns
cols = cols.insert(0, "constant")
gamingaccessory_lr_coef = list(zip(cols, gamingaccessory_lr_model_parameters))
gamingaccessory_lr_coef
Out[362]:
In [363]:
gamingaccessory_lr_coef_df = pd.DataFrame(gamingaccessory_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.rename(columns=col_rename)
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.iloc[1:,:]
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.loc[gamingaccessory_lr_coef_df['Coefficients']!=0.0]
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
gamingaccessory_lr_coef_df
Out[363]:
Plotting the Features in descending order of Importance for gamingaccessory¶
In [364]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 35), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=gamingaccessory_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for gamingaccessory are:¶
| Features | Coefficients |
|---|---|
| product_vertical_gamepad | 0.150 |
| product_vertical_gamingaccessorykit | 0.115 |
| is_mass_market | 0.109 |
| product_vertical_motioncontroller | 0.100 |
| product_vertical_gamingkeyboard | 0.089 |
Building Linear Regression model for homeaudio¶
In [365]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
homeaudio_dladd_model = LinearRegression().fit(X_homeaudio_dladd_train, y_homeaudio_dladd_train)
y_homeaudio_dladd_test_pred = homeaudio_dladd_model.predict(X_homeaudio_dladd_test)
print('R2 Score: {}'.format(r2_score(y_homeaudio_dladd_test, y_homeaudio_dladd_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_homeaudio_dladd_test, y_homeaudio_dladd_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.42 and mse of 1.39¶
Building Linear Regression model for homeaudio using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [366]:
y_homeaudio_dladd = homeaudio_dladd_df.pop('gmv')
X_homeaudio_dladd = homeaudio_dladd_df
In [367]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
homeaudio_dladd_model_cv = LinearRegression().fit(X_homeaudio_dladd, y_homeaudio_dladd)
homeaudio_dladd_predictions_cv = cross_val_predict(homeaudio_dladd_model_cv, X_homeaudio_dladd, y_homeaudio_dladd, cv=10)
accuracy = metrics.r2_score(y_homeaudio_dladd, homeaudio_dladd_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_homeaudio_dladd, homeaudio_dladd_predictions_cv)))
With Simple Linear Regression, using cross validation, we get r2 score of 0.55 and mse score of 0.45¶
Determining Feature Importance for homeaudio¶
In [368]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
homeaudio_lr_model_parameters = list(homeaudio_dladd_model_cv.coef_)
homeaudio_lr_model_parameters.insert(0, homeaudio_dladd_model_cv.intercept_)
homeaudio_lr_model_parameters = [round(x, 3) for x in homeaudio_lr_model_parameters]
cols = homeaudio_dladd_test.columns
cols = cols.insert(0, "constant")
homeaudio_lr_coef = list(zip(cols, homeaudio_lr_model_parameters))
homeaudio_lr_coef
Out[368]:
In [369]:
homeaudio_lr_coef_df = pd.DataFrame(homeaudio_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
homeaudio_lr_coef_df = homeaudio_lr_coef_df.rename(columns=col_rename)
homeaudio_lr_coef_df = homeaudio_lr_coef_df.iloc[1:,:]
homeaudio_lr_coef_df = homeaudio_lr_coef_df.loc[homeaudio_lr_coef_df['Coefficients']!=0.0]
homeaudio_lr_coef_df = homeaudio_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
homeaudio_lr_coef_df
Out[369]:
Plotting the Features in descending order of Importance for homeaudio¶
In [370]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 35), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=homeaudio_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for homeaudio are:¶
| Features | Coefficients |
|---|---|
| product_vertical_homeaudiospeaker | 0.184 |
| product_vertical_karaokeplayer | 0.173 |
| is_mass_market | 0.162 |
| is_cod | 0.145 |
| product_vertical_fmradio | 0.094 |
In [ ]:
In [ ]:
Distributive Lag Model(Multiplicative)¶
The Distributive Lag Model(Additive) helped us capture the not only the current, but also the carry-over effect of all the variables(depedent and independent).
Yt = α+ µ1Yt-1 + µ2Yt-2 + µ3Yt-3 + ....
+ β1X1t + β1X1t-1 + β1X1t-2 + ....
+ β2X2t + β2X2t-1 + β2X2t-2 + ....
+ β3X3t + β3X3t-1 + β3X3t-2 + ....
+ β4X4t + β4X4t-1 + β4X4t-2 + ....
+ β5X5t + β5X5t-1 + β5X5t-2 + ....
+ ϵ
Now the Distributive Lag Model(Multiplicative) will help us capture the interactions between current and carry over effects of the KPIs.
To fit a multiplicative model, take logarithms of the data(on both sides of the model), then analyse the log data as before.
Yt = α+ µ1ln(Yt-1) + µ2ln(Yt-2) + µ3ln(Yt-3) + ....
+ β1ln(X1t) + β1ln(X1t-1) + β1ln(X1t-2) + ....
+ β2ln(X2t) + β2ln(X2t-1) + β2ln(X2t-2) + ....
+ β3ln(X3t) + β3ln(X3t-1) + β3ln(X3t-2) + ....
+ β4ln(X4t) + β4ln(X4t-1) + β4ln(X4t-2) + ....
+ β5ln(X5t) + β5ln(X5t-1) + β5ln(X5t-2) + ....
+ ϵ'In [371]:
homeaudio_org_df.head()
Out[371]:
In [372]:
# Making copy of dataframes from the original ones
cameraaccessory_dlmul_df = cameraaccessory_org_df.copy()
gamingaccessory_dlmul_df = gamingaccessory_org_df.copy()
homeaudio_dlmul_df = homeaudio_org_df.copy()
homeaudio_dlmul_df.head()
Out[372]:
In [373]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(homeaudio_dladd_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(homeaudio_dladd_df.isnull().sum()/homeaudio_dladd_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[373]:
We will drop the Week column as it is a row identifier and will not help in prediction of revenue¶
In [374]:
# removing columns
cameraaccessory_dlmul_df = cameraaccessory_dlmul_df.drop('Week', axis=1)
gamingaccessory_dlmul_df = gamingaccessory_dlmul_df.drop('Week', axis=1)
homeaudio_dlmul_df = homeaudio_dlmul_df.drop('Week', axis=1)
homeaudio_dlmul_df.head()
Out[374]:
Creating new lag(Lag of 3 weeks) variables for the dependent variable (GMV) as well as independent variables¶
In [375]:
cameraaccessory_dlmul_df_columns = cameraaccessory_dlmul_df.columns
gamingaccessory_dlmul_df_columns = gamingaccessory_dlmul_df.columns
homeaudio_dlmul_df_columns = homeaudio_dlmul_df.columns
In [376]:
cameraaccessory_dlmul_df = lag_variables(cameraaccessory_dlmul_df,cameraaccessory_dlmul_df_columns,3)
gamingaccessory_dlmul_df = lag_variables(gamingaccessory_dlmul_df,gamingaccessory_dlmul_df_columns,3)
homeaudio_dlmul_df = lag_variables(homeaudio_dlmul_df,homeaudio_dlmul_df_columns,3)
homeaudio_dlmul_df.head()
Out[376]:
Creating new lag(Lag of 2 weeks) variables for the dependent variable (GMV) as well as independent variables¶
In [377]:
cameraaccessory_dlmul_df = lag_variables(cameraaccessory_dlmul_df,cameraaccessory_dlmul_df_columns,2)
gamingaccessory_dlmul_df = lag_variables(gamingaccessory_dlmul_df,gamingaccessory_dlmul_df_columns,2)
homeaudio_dlmul_df = lag_variables(homeaudio_dlmul_df,homeaudio_dlmul_df_columns,2)
homeaudio_dlmul_df.head()
Out[377]:
Creating new lag(Lag of 1 week) variables for the dependent variable (GMV) as well as independent variables¶
In [378]:
cameraaccessory_dlmul_df = lag_variables(cameraaccessory_dlmul_df,cameraaccessory_dlmul_df_columns,1)
gamingaccessory_dlmul_df = lag_variables(gamingaccessory_dlmul_df,gamingaccessory_dlmul_df_columns,1)
homeaudio_dlmul_df = lag_variables(homeaudio_dlmul_df,homeaudio_dlmul_df_columns,1)
homeaudio_dlmul_df.head()
Out[378]:
Imputing all null values with 0¶
In [379]:
# Imputing all null values with 0
cameraaccessory_dlmul_df.fillna(value=0, inplace=True)
gamingaccessory_dlmul_df.fillna(value=0, inplace=True)
homeaudio_dlmul_df.fillna(value=0, inplace=True)
homeaudio_dlmul_df.head(10)
Out[379]:
In [380]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(homeaudio_dladd_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(homeaudio_dladd_df.isnull().sum()/homeaudio_dladd_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[380]:
In [381]:
cameraaccessory_dlmul_df = cameraaccessory_dlmul_df.applymap(lambda x: np.log(x))
cameraaccessory_dlmul_df = cameraaccessory_dlmul_df.replace([np.inf, -np.inf], 0)
cameraaccessory_dlmul_df = cameraaccessory_dlmul_df.replace(np.nan, 0)
gamingaccessory_dlmul_df = gamingaccessory_dlmul_df.applymap(lambda x: np.log(x))
gamingaccessory_dlmul_df = gamingaccessory_dlmul_df.replace([np.inf, -np.inf], 0)
gamingaccessory_dlmul_df = gamingaccessory_dlmul_df.replace(np.nan, 0)
homeaudio_dlmul_df = homeaudio_dlmul_df.applymap(lambda x: np.log(x))
homeaudio_dlmul_df = homeaudio_dlmul_df.replace([np.inf, -np.inf], 0)
homeaudio_dlmul_df = homeaudio_dlmul_df.replace(np.nan, 0)
homeaudio_dlmul_df.head()
Out[381]:
In [382]:
# Checking for total count and percentage of null values in all columns of the dataframe.
total = pd.DataFrame(homeaudio_dlmul_df.isnull().sum().sort_values(ascending=False), columns=['Total'])
percentage = pd.DataFrame(round(100*(homeaudio_dlmul_df.isnull().sum()/homeaudio_dlmul_df.shape[0]),2).sort_values(ascending=False)\
,columns=['Percentage'])
pd.concat([total, percentage], axis = 1).head()
Out[382]:
Rescaling the Features of the 3 Dataframes¶
We will use Standard scaling.
In [383]:
from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
cameraaccessory_dlmul_df[cameraaccessory_dlmul_df.columns]=scaler.fit_transform(cameraaccessory_dlmul_df[cameraaccessory_dlmul_df.columns])
gamingaccessory_dlmul_df[gamingaccessory_dlmul_df.columns]=scaler.fit_transform(gamingaccessory_dlmul_df[gamingaccessory_dlmul_df.columns])
homeaudio_dlmul_df[homeaudio_dlmul_df.columns]=scaler.fit_transform(homeaudio_dlmul_df[homeaudio_dlmul_df.columns])
homeaudio_dlmul_df.head()
Out[383]:
Splitting the 3 Dataframes into Training and Testing Sets¶
As you know, the first basic step for regression is performing a train-test split.
In [384]:
from sklearn.model_selection import train_test_split
# We specify this so that the train and test data set always have the same rows, respectively
cameraaccessory_dlmul_train, cameraaccessory_dlmul_test = train_test_split(cameraaccessory_dlmul_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
gamingaccessory_dlmul_train, gamingaccessory_dlmul_test = train_test_split(gamingaccessory_dlmul_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
homeaudio_dlmul_train, homeaudio_dlmul_test = train_test_split(homeaudio_dlmul_df, \
train_size = 0.7, test_size = 0.3, random_state = 100)
Dividing the 3 dataframes into X and Y sets for the model building¶
In [385]:
y_cameraaccessory_dlmul_train = cameraaccessory_dlmul_train.pop('gmv')
X_cameraaccessory_dlmul_train = cameraaccessory_dlmul_train
y_gamingaccessory_dlmul_train = gamingaccessory_dlmul_train.pop('gmv')
X_gamingaccessory_dlmul_train = gamingaccessory_dlmul_train
y_homeaudio_dlmul_train = homeaudio_dlmul_train.pop('gmv')
X_homeaudio_dlmul_train = homeaudio_dlmul_train
X_homeaudio_dlmul_train.head()
Out[385]:
Dividing into X and Y test sets for the model building for 3 dataframes¶
In [386]:
y_cameraaccessory_dlmul_test = cameraaccessory_dlmul_test.pop('gmv')
X_cameraaccessory_dlmul_test = cameraaccessory_dlmul_test
y_gamingaccessory_dlmul_test = gamingaccessory_dlmul_test.pop('gmv')
X_gamingaccessory_dlmul_test = gamingaccessory_dlmul_test
y_homeaudio_dlmul_test = homeaudio_dlmul_test.pop('gmv')
X_homeaudio_dlmul_test = homeaudio_dlmul_test
X_homeaudio_dlmul_test.head()
Out[386]:
Building Linear Regression model for cameraaccessory¶
In [387]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
cameraaccessory_dlmul_model = LinearRegression().fit(X_cameraaccessory_dlmul_train, y_cameraaccessory_dlmul_train)
y_cameraaccessory_dlmul_test_pred = cameraaccessory_dlmul_model.predict(X_cameraaccessory_dlmul_test)
print('R2 Score: {}'.format(r2_score(y_cameraaccessory_dlmul_test, y_cameraaccessory_dlmul_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_cameraaccessory_dlmul_test, y_cameraaccessory_dlmul_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.77 and mse of 0.50¶
Building Linear Regression model for cameraaccessory using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [388]:
y_cameraaccessory_dlmul = cameraaccessory_dlmul_df.pop('gmv')
X_cameraaccessory_dlmul = cameraaccessory_dlmul_df
In [389]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
cameraaccessory_dlmul_model_cv = LinearRegression().fit(X_cameraaccessory_dlmul, y_cameraaccessory_dlmul)
cameraaccessory_dlmul_predictions_cv = cross_val_predict(cameraaccessory_dlmul_model_cv, X_cameraaccessory_dlmul, \
y_cameraaccessory_dlmul, cv=10)
accuracy = metrics.r2_score(y_cameraaccessory_dlmul, cameraaccessory_dlmul_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_cameraaccessory_dlmul, cameraaccessory_dlmul_predictions_cv)))
With Simple Linear Regression, using cross validation, we get r2 score of 0.82 and mse score of 0.18¶
Determining Feature Importance for cameraaccessory with model with cv¶
In [390]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
cameraaccessory_lr_model_parameters = list(cameraaccessory_dlmul_model_cv.coef_)
cameraaccessory_lr_model_parameters.insert(0, cameraaccessory_dlmul_model_cv.intercept_)
cameraaccessory_lr_model_parameters = [round(x, 3) for x in cameraaccessory_lr_model_parameters]
cols = X_cameraaccessory_dlmul_test.columns
cols = cols.insert(0, "constant")
cameraaccessory_lr_coef = list(zip(cols, cameraaccessory_lr_model_parameters))
cameraaccessory_lr_coef
Out[390]:
In [391]:
cameraaccessory_lr_coef_df = pd.DataFrame(cameraaccessory_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.rename(columns=col_rename)
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.iloc[1:,:]
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.loc[cameraaccessory_lr_coef_df['Coefficients']!=0.0]
cameraaccessory_lr_coef_df = cameraaccessory_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
cameraaccessory_lr_coef_df
Out[391]:
Plotting the Features in descending order of Importance for cameraaccessory¶
In [392]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 35), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=cameraaccessory_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for cameraaccessory are:¶
| Features | Coefficients |
|---|---|
| is_mass_market | 0.061 |
| product_vertical_lens | 0.060 |
| product_vertical_cameraaccessory | 0.060 |
| product_vertical_camerabattery | 0.059 |
| product_vertical_cameratripod | 0.059 |
Building Linear Regression model for gamingaccessory¶
In [393]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
gamingaccessory_dlmul_model = LinearRegression().fit(X_gamingaccessory_dlmul_train, y_gamingaccessory_dlmul_train)
y_gamingaccessory_dlmul_test_pred = gamingaccessory_dlmul_model.predict(X_gamingaccessory_dlmul_test)
print('R2 Score: {}'.format(r2_score(y_gamingaccessory_dlmul_test, y_gamingaccessory_dlmul_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_gamingaccessory_dlmul_test, y_gamingaccessory_dlmul_test_pred)))
With Simple Linear Regression, we get a r2 score of 0.93 and mse score of 0.11¶
Building Linear Regression model for gamingaccessory using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [394]:
y_gamingaccessory_dlmul = gamingaccessory_dlmul_df.pop('gmv')
X_gamingaccessory_dlmul = gamingaccessory_dlmul_df
In [395]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
gamingaccessory_dlmul_model_cv = LinearRegression().fit(X_gamingaccessory_dlmul, y_gamingaccessory_dlmul)
gamingaccessory_dlmul_predictions_cv = cross_val_predict(gamingaccessory_dlmul_model_cv, X_gamingaccessory_dlmul, \
y_gamingaccessory_dlmul, cv=10)
accuracy = metrics.r2_score(y_gamingaccessory_dlmul, gamingaccessory_dlmul_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_gamingaccessory_dlmul, gamingaccessory_dlmul_predictions_cv)))
With Simple Linear Regression, using cross validation, we get r2 score of 0.89 and mse score of 0.11¶
Determining Feature Importance for gamingaccessory with model with cv¶
In [396]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
gamingaccessory_lr_model_parameters = list(gamingaccessory_dlmul_model_cv.coef_)
gamingaccessory_lr_model_parameters.insert(0, gamingaccessory_dlmul_model_cv.intercept_)
gamingaccessory_lr_model_parameters = [round(x, 3) for x in gamingaccessory_lr_model_parameters]
cols = X_gamingaccessory_dlmul_test.columns
cols = cols.insert(0, "constant")
gamingaccessory_lr_coef = list(zip(cols, gamingaccessory_lr_model_parameters))
gamingaccessory_lr_coef
Out[396]:
In [397]:
gamingaccessory_lr_coef_df = pd.DataFrame(gamingaccessory_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.rename(columns=col_rename)
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.iloc[1:,:]
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.loc[gamingaccessory_lr_coef_df['Coefficients']!=0.0]
gamingaccessory_lr_coef_df = gamingaccessory_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
gamingaccessory_lr_coef_df
Out[397]:
Plotting the Features in descending order of Importance for gamingaccessory¶
In [398]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 35), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=gamingaccessory_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for gamingaccessory are:¶
| Features | Coefficients |
|---|---|
| product_vertical_gamepad | 0.088 |
| product_vertical_gamingmouse | 0.085 |
| is_mass_market | 0.082 |
| product_vertical_gamingkeyboard | 0.074 |
| is_cod | 0.072 |
Building Linear Regression model for homeaudio¶
In [399]:
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
homeaudio_dlmul_model = LinearRegression().fit(X_homeaudio_dlmul_train, y_homeaudio_dlmul_train)
y_homeaudio_dlmul_test_pred = homeaudio_dlmul_model.predict(X_homeaudio_dlmul_test)
print('R2 Score: {}'.format(r2_score(y_homeaudio_dlmul_test, y_homeaudio_dlmul_test_pred)))
print('Mean Squared Error: {}'.format(mean_squared_error(y_homeaudio_dlmul_test, y_homeaudio_dlmul_test_pred)))
With Simple Linear Regression, we get a r2 score of -0.23 and mse of 0.26¶
Here R2 is negative which signifies that the chosen model does not follow the trend of the data, so fits worse than a horizontal line. It simply means the chosen model (with its constraints) fits the data really poorly.
Building Linear Regression model for homeaudio using K-fold Cross Validation¶
We will use GridSearchCV method and 5 fold cross validation method for our linear regression.
In [400]:
y_homeaudio_dlmul = homeaudio_dlmul_df.pop('gmv')
X_homeaudio_dlmul = homeaudio_dlmul_df
In [401]:
# Make cross validated predictions
from sklearn.model_selection import cross_val_score,cross_val_predict
from sklearn import metrics
homeaudio_model_dlmul_cv = LinearRegression().fit(X_homeaudio_dlmul, y_homeaudio_dlmul)
homeaudio_dlmul_predictions_cv = cross_val_predict(homeaudio_model_dlmul_cv, X_homeaudio_dlmul, y_homeaudio_dlmul, cv=5)
accuracy = metrics.r2_score(y_homeaudio_dlmul, homeaudio_dlmul_predictions_cv)
print("Cross-Predicted Accuracy:", accuracy)
print('Mean Squared Error: {}'.format(mean_squared_error(y_homeaudio_dlmul, homeaudio_dlmul_predictions_cv)))
With Simple Linear Regression, using cross validation, we get r2 score of 0.57 and mse score of 0.43¶
Determining Feature Importance for homeaudio with model with cv¶
In [402]:
# linear regression model parameters
#Limiting floats output to 3 decimal points
pd.set_option('display.float_format', lambda x: '{:.3f}'.format(x))
pd.set_option('display.precision',1)
homeaudio_lr_model_parameters = list(homeaudio_model_dlmul_cv.coef_)
homeaudio_lr_model_parameters.insert(0, homeaudio_model_dlmul_cv.intercept_)
homeaudio_lr_model_parameters = [round(x, 3) for x in homeaudio_lr_model_parameters]
cols = X_homeaudio_dlmul_test.columns
cols = cols.insert(0, "constant")
homeaudio_lr_coef = list(zip(cols, homeaudio_lr_model_parameters))
homeaudio_lr_coef
Out[402]:
In [403]:
homeaudio_lr_coef_df = pd.DataFrame(homeaudio_lr_coef)
col_rename = {0:'Features',1: 'Coefficients'}
homeaudio_lr_coef_df = homeaudio_lr_coef_df.rename(columns=col_rename)
homeaudio_lr_coef_df = homeaudio_lr_coef_df.iloc[1:,:]
homeaudio_lr_coef_df = homeaudio_lr_coef_df.loc[homeaudio_lr_coef_df['Coefficients']!=0.0]
homeaudio_lr_coef_df = homeaudio_lr_coef_df.sort_values(by=['Coefficients'], ascending = False)
homeaudio_lr_coef_df
Out[403]:
Plotting the Features in descending order of Importance for homeaudio¶
In [404]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(10, 35), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.barplot(y='Features', x='Coefficients', palette='husl', data=homeaudio_lr_coef_df, estimator=np.sum)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
plt.tight_layout()
# display the plot
plt.show()
The 5 most important features affecting GMV(Revenue) for homeaudio are:¶
| Features | Coefficients |
|---|---|
| product_vertical_homeaudiospeaker | 0.136 |
| is_mass_market | 0.133 |
| product_vertical_fmradio | 0.122 |
| is_cod | 0.112 |
| product_vertical_voicerecorder | 0.104 |
Model Dashboard¶
| Product Sub-category | Linear Regression Model | Cross Validation | R-square on Test Dataset | Mean Square Error |
|---|---|---|---|---|
| __cameraaccessory__ | Additive | No | 0.83 | 0.17 |
| Yes | -0.8 | 1.08 | ||
| Multiplicative | No | 0.84 | 0.36 | |
| Yes | 0.91 | 0.09 | ||
| Koyck | No | 0.84 | 0.16 | |
| Yes | 0.27 | 0.73 | ||
| Distributive Lag Model (Additive) | No | 0.87 | 0.12 | |
| Yes | 0.82 | 0.17 | ||
| Distributive Lag Model (Multiplicaitive) | No | 0.77 | 0.50 | |
| Yes | 0.82 | 0.18 | ||
| __gamingaccessory__ | Additive | No | 0.93 | 0.05 |
| Yes | 0.51 | 0.49 | ||
| Multiplicative | No | 0.94 | 0.09 | |
| Yes | 0.94 | 0.06 | ||
| Koyck | No | 0.93 | 0.05 | |
| Yes | 0.49 | 0.51 | ||
| Distributive Lag Model (Additive) | No | 0.87 | 0.10 | |
| Yes | 0.92 | 0.08 | ||
| Distributive Lag Model (Multiplicaitive) | No | 0.93 | 0.11 | |
| Yes | 0.89 | 0.11 | ||
| __homeaudio__ | Additive | No | 0.96 | 0.09 |
| Yes | 0.73 | 0.27 | ||
| Multiplicative | No | -0.63 | 0.34 | |
| Yes | 0.86 | 0.14 | ||
| Koyck | No | 0.96 | 0.09 | |
| Yes | 0.70 | 0.30 | ||
| Distributive Lag Model (Additive) | No | 0.42 | 1.39 | |
| Yes | 0.55 | 0.45 | ||
| Distributive Lag Model (Multiplicaitive) | No | -0.23 | 0.26 | |
| Yes | 0.57 | 0.43 |
Model Selection¶
The criteria of choosing the model is based on the accuracy parameters -- R2 score & MSE score -- and the business relevance of the important attributes chosen by the model. Also we tried to choose models with cross validation because even though the ones without, sometimes gives us good scores, they are not very dependable & generalisable, owing to limited dataset.¶
By referring the above model dashboard, we finalize the following models for the 3 mentioned product subcategories - Camera Accessory, Gaming Accessory & Home Audio:
| Product Sub-category | Linear Regression Model | R-square on Test Dataset | Mean Square Error | Top 5 KPIs |
|---|---|---|---|---|
| __cameraaccessory__ | Multiplicative with CV | 0.91 | 0.09 | product_vertical_lens (__0.181__) |
| product_vertical_camerabattery (__0.160__) | ||||
| is_mass_market (__0.149__) | ||||
| product_vertical_camerabatterycharger (__0.121__) | ||||
| TV (__0.105__) | ||||
| __gamingaccessory__ | Multiplicative with CV | 0.94 | 0.06 | product_vertical_gamingheadset (__0.250__) |
| is_mass_market (__0.234__) | ||||
| product_vertical_gamingmouse (__0.224__) | ||||
| product_vertical_gamepad (__0.211__) | ||||
| Online marketing_SMA_3 (__0.157__) | ||||
| __cameraaccessory__ | Multiplicative with CV | 0.86 | 0.14 | product_vertical_homeaudiospeaker (__0.469__) |
| is_mass_market (__0.289__) | ||||
| product_vertical_fmradio (__0.224__) | ||||
| Radio_Ad_Stock (__0.147__) | ||||
| Sponsorship (__0.121__) |
We notice that all the 3 chosen models for the 3 sub-categories are Multiplicative models. This fact tells us that there exists some interaction between the KPIs. These models tell us about the growth of revenue vs the interactive growth of the KPIs.
Model Evaluation¶
Evaluating the Final Models:¶
In [405]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(12, 4), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
# subplot 1
plt.subplot(1, 3, 1)
c = [i for i in range(1,51,1)]
plt.plot(c,y_cameraaccessory_mul, color="blue", linewidth=2.5, linestyle="-")
plt.plot(c,cameraaccessory_mul_predictions_cv, color="red", linewidth=2.5, linestyle="-")
plt.suptitle('Actual vs Predicted', fontsize=16, color = 'c') # Plot heading
plt.title('Camera Accessory', fontsize=12) # Plot heading
plt.xlabel('Index', fontsize=12) # X-label
plt.ylabel('Views', fontsize=12) # Y-label
# subplot 2
plt.subplot(1, 3, 2)
c = [i for i in range(1,52,1)]
plt.plot(c,y_gamingaccessory_mul, color="blue", linewidth=2.5, linestyle="-")
plt.plot(c,gamingaccessory_mul_predictions_cv, color="red", linewidth=2.5, linestyle="-")
plt.title('Gaming Accessory', fontsize=12)
plt.xlabel('Index', fontsize=12) # X-label
plt.ylabel('Views', fontsize=12) # Y-label
# subplot 3
plt.subplot(1, 3, 3)
c = [i for i in range(1,49,1)]
plt.plot(c,y_homeaudio_mul, color="blue", linewidth=2.5, linestyle="-")
plt.plot(c,homeaudio_mul_predictions_cv, color="red", linewidth=2.5, linestyle="-")
plt.title('Home Audio', fontsize=12)
plt.xlabel('Index', fontsize=12) # X-label
plt.ylabel('Views', fontsize=12) # Y-label
# display the plot
plt.show()
Plotting the actual and predicted price values from the dataset to check the likeness¶
In [406]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(12, 4), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
# subplot 1
plt.subplot(1, 3, 1)
# Plotting y_test and y_pred to understand the spread.
plt.scatter(y_cameraaccessory_mul,cameraaccessory_mul_predictions_cv)
plt.suptitle('y_actual vs y_predicted', fontsize=16, color = 'c') # Plot heading
plt.title('Camera Accessory', fontsize=12) # Plot heading
plt.xlabel('y_actual', fontsize=12) # X-label
plt.ylabel('y_pred', fontsize=12) # Y-label
# subplot 2
plt.subplot(1, 3, 2)
# Plotting y_test and y_pred to understand the spread.
plt.scatter(y_gamingaccessory_mul,gamingaccessory_mul_predictions_cv)
plt.title('Camera Accessory', fontsize=12) # Plot heading
plt.xlabel('y_actual', fontsize=12) # X-label
plt.ylabel('y_pred', fontsize=12) # Y-label
# subplot 3
plt.subplot(1, 3, 3)
# Plotting y_test and y_pred to understand the spread.
plt.scatter(y_homeaudio_mul,homeaudio_mul_predictions_cv)
plt.title('Camera Accessory', fontsize=12) # Plot heading
plt.xlabel('y_actual', fontsize=12) # X-label
plt.ylabel('y_pred', fontsize=12) # Y-label
# display the plot
plt.show()
Drawing a scatter plot with actual and predicted price values from the dataset to check the spread¶
In [407]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(12, 4), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
# subplot 1
plt.subplot(1, 3, 1)
# Error terms
c = [i for i in range(1,51,1)]
plt.scatter(c,y_cameraaccessory_mul - cameraaccessory_mul_predictions_cv)
plt.suptitle('Error Terms', fontsize=16, color = 'c') # Plot heading
plt.title('Camera Accessory', fontsize=12) # Plot heading
plt.xlabel('Index', fontsize=12) # X-label
plt.ylabel('y_act - y_pred', fontsize=12) # Y-label
# subplot 2
plt.subplot(1, 3, 2)
# Error terms
c = [i for i in range(1,52,1)]
plt.scatter(c,y_gamingaccessory_mul - gamingaccessory_mul_predictions_cv)
plt.title('Gaming Accessory', fontsize=12) # Plot heading
plt.xlabel('Index', fontsize=12) # X-label
# subplot 3
plt.subplot(1, 3, 3)
# Error terms
c = [i for i in range(1,49,1)]
plt.scatter(c,y_homeaudio_mul - homeaudio_mul_predictions_cv)
plt.title('Home Audio', fontsize=12) # Plot heading
plt.xlabel('Index', fontsize=12) # X-label
# display the plot
plt.show()
Drawing a scatter plot of the error terms to check the spread to ensure that the error terms have constant variance (homoscedasticity). The variance doesn't increase (or decrease) or follow a pattern as the error values change.¶
In [408]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(12, 4), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
# subplot 1
plt.subplot(1, 3, 1)
# Plot the histogram of the error terms
sns.distplot((y_cameraaccessory_mul-cameraaccessory_mul_predictions_cv), bins = 20)
fig.suptitle('Error Terms', fontsize = 16) # Plot heading
plt.title('Camera Accessory', fontsize=12) # Plot heading
plt.xlabel('Errors', fontsize = 12) # X-label
# subplot 2
plt.subplot(1, 3, 2)
# Plot the histogram of the error terms
sns.distplot((y_gamingaccessory_mul-gamingaccessory_mul_predictions_cv), bins = 20)
plt.title('Gaming Accessory', fontsize=12) # Plot heading
plt.xlabel('Errors', fontsize = 12) # X-label
# subplot 3
plt.subplot(1, 3, 3)
# Plot the histogram of the error terms
sns.distplot((y_homeaudio_mul-homeaudio_mul_predictions_cv), bins = 20)
plt.title('Home Audio', fontsize=12) # Plot heading
plt.xlabel('Errors', fontsize = 12) # X-label
# display the plot
plt.show()
Plotting the distribution of the error terms. The error terms follow a normal distrbution with mean at 0 barring a few outlier values.¶
In [409]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(12, 4), dpi=100, facecolor='w', edgecolor='k', frameon='True')
sns.set_style("white") # white/whitegrid/dark/ticks
sns.set_context("paper") # talk/poster
# subplot 1
plt.subplot(1, 3, 1)
# Plot the histogram of the error terms
sns.regplot(y_cameraaccessory_mul, cameraaccessory_mul_predictions_cv)
plt.suptitle('Best Fitted Line', fontsize=16, color = 'c') # Plot heading
plt.title('Camera Accessory', fontsize=12) # Plot heading
plt.xlabel('y_actual', fontsize=12) # X-label
plt.ylabel('y_pred', fontsize=12) # Y-label
# subplot 2
plt.subplot(1, 3, 2)
# Plot the histogram of the error terms
sns.regplot(y_gamingaccessory_mul, gamingaccessory_mul_predictions_cv)
plt.title('Gaming Accessory', fontsize=12) # Plot heading
plt.xlabel('y_actual', fontsize=12) # X-label
plt.ylabel('y_pred', fontsize=12) # Y-label
# subplot 3
plt.subplot(1, 3, 3)
# Plot the histogram of the error terms
sns.regplot(y_homeaudio_mul, homeaudio_mul_predictions_cv)
plt.title('Home Audio', fontsize=12) # Plot heading
plt.xlabel('y_actual', fontsize=12) # X-label
plt.ylabel('y_pred', fontsize=12) # Y-label
# display the plot
plt.show()
Plotting a scatter plot with actual and predicted price values from the dataset to check the spread and drawing the best fitted line through it.¶
Model Equation¶
Considering the top 5 KPIs from the models for our 3 product subcategories, we can see that the equation of our best fitted lines as follows:¶
Camera Accessory:
Revenue = 0.0 + (0.181 × __product_vertical_lens__) + (0.160 × __product_vertical_camerabattery__) + (0.149 × __is_mass_market__) + (0.121 × __product_vertical_camerabatterycharger__) + (0.105 × __TV__)
__Gaming Accessory:__
Revenue = 0.0 + (0.250 × __product_vertical_gamingheadset__) + (0.234 × __is_mass_market__) + (0.224 × __product_vertical_gamingmouse__) + (0.211 × __product_vertical_gamepad__) + (0.157 × __Online marketing_SMA_3__)
__Home Audio:__
Revenue = 0.0 + (0.469 × __product_vertical_homeaudiospeaker__) + (0.289 × __is_mass_market__) + (0.224 × __product_vertical_fmradio__) + (0.147 × __Radio_Ad_Stock__) + (0.121 × __Sponsorship__)
This equation implies how much the Revenue will grow with a unit growth in any of these independent KPIs with all other KPIs held constant.¶
Recommendation¶
In [410]:
cameraaccessory_mul_lr_coef_df = cameraaccessory_mul_lr_coef_df.head().append(cameraaccessory_mul_lr_coef_df.tail())
gamingaccessory_mul_lr_coef_df = gamingaccessory_mul_lr_coef_df.head().append(gamingaccessory_mul_lr_coef_df.tail())
homeaudio_mul_lr_coef_df = homeaudio_mul_lr_coef_df.head().append(homeaudio_mul_lr_coef_df.tail())
gamingaccessory_mul_lr_coef_df
Out[410]:
Plotting the Top 5 features that affect Each of the 3 Product Sub-categories(both positively and adversely) as per our Chosen Models¶
In [411]:
# Slightly alter the figure size to make it more horizontal.
plt.figure(figsize=(15,6), dpi=100, facecolor='w', edgecolor='k', frameon='True')
# subplot 1
plt.subplot(1, 3, 1)
sns.barplot(x='Features', y='Coefficients', color = 'tab:red',alpha=0.8, data=cameraaccessory_mul_lr_coef_df, estimator=np.sum)
plt.title('Camera Accessories', fontsize=12, alpha=0.8)
plt.xticks(rotation='vertical', fontsize=10)
# subplot 1
plt.subplot(1, 3, 2)
sns.barplot(x='Features', y='Coefficients', color = 'tab:red',alpha=0.8, data=gamingaccessory_mul_lr_coef_df, estimator=np.sum)
plt.title('Gaming Accessories', fontsize=12, alpha=0.8)
plt.xticks(rotation='vertical', fontsize=10)
# subplot 1
plt.subplot(1, 3, 3)
sns.barplot(x='Features', y='Coefficients', color = 'tab:red',alpha=0.8, data=homeaudio_mul_lr_coef_df, estimator=np.sum)
plt.title('Home Audio', fontsize=12, alpha=0.8)
plt.suptitle('Top 5 features that affect Each of the 3 Product Sub-categories(both positively and adversely) \
as per our Chosen Models', fontsize=15, color = 'r', alpha=1)
plt.xticks(rotation='vertical', fontsize=10)
# Automatically adjust subplot params so that the subplotS fits in to the figure area.
#plt.tight_layout()
# display the plot
plt.show()
Recommendations¶
Camera Accessory:
- Company should promote
Lens,Camera Batteries&Camera Battery Chargersas they fetch the highest revenue. - Advertisement spends on
TVhas a positive impact on revenue. One unit of TV spend can boost the revenue by 0.105 units.Content Marketingspends on the other hand impacts negatively. Mass-marketproducts are better contributors to the increased revenue in comparison to the Luxury products.- Higher percentage of
Discountsin general given for this sub category works adversely towards bringing down the revenue.
__Gaming Accessory:__
- Company should promote
Gaming Headset,Gaming Mouse&Gamepadas they fetch the highest revenue. On the contrary,Gaming Memory Cardsresults in loss. - Advertisement spends on
Online Marketing, Radio & Othershave a positive cumulative impact on revenue.Sponsorshipspends on the other hand has a negative cumulative effect. Mass-marketproducts are better contributors to the increased revenue in comparison to the Luxury products.- Higher percentage of
Discountsin general given for this sub category works adversely towards bringing down the revenue.
__Home Audio:__
- Company should promote
Home Audio Speakers& `FM Radios as they fetch the highest revenue. Mass-marketproducts are better contributors to the increased revenue in comparison to the Luxury products.Radio Adstock(carry over effect of Radio Advertisement) spends helps to boost the revenue to a significant extent.- Advertisement spends on
Sponsorshiphas a positive impact on revenue.Content Marketingspends on the other hand impacts negatively. COD paymentsin general for this sub category are bad in bringing down the revenue.
__In General:__
- Most of the sales take place when Discount% is between 50-60%. However, that doesnt necessarily help in boosting the revenue. EDA shows that an average discount% between 10-20% is the most profitable for the company specially among luxury items.
- In general most of the Home Audio items sold are luxury items and hence, customers prefer to use COD instead of paying upfront.
- During festive time(eg. Thanksgiving) more investment is made on Advertisement and good promotional offers were rolled out. This usually boosts the revenue. However just providing discounts without properly adertising for it on several media channels doesn't help. We have seen that for the weeks 32 - 35(August), revenue generated was the lowest from all 3 product subcategories even though median discount% was raised after the initial drought. In fact, this dip in revenue can be observed as a direct relation to minimum amount of total investment in Ads during the given timeframe.